{"id":2724,"date":"2012-10-12T01:21:07","date_gmt":"2012-10-12T01:21:07","guid":{"rendered":"http:\/\/craftydba.com\/?p=2724"},"modified":"2012-12-23T23:50:26","modified_gmt":"2012-12-23T23:50:26","slug":"basic-training-data-types-6","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2724","title":{"rendered":"Basic Training – Data Types – Part 6"},"content":{"rendered":"

\"\"<\/a><\/p>\n

<\/P>
\nI am continuing my series of talks on fundamental topics like data types. I am proud to be a United States Army Reservist (USAR<\/a>) Veteran. <\/p>\n

Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING<\/a>.<\/p>\n

<\/P><\/p>\n

<\/P><\/p>\n

<\/P><\/p>\n

The most basic unit of any database is a TABLE which consists of COLUMNS. A vital decision during the initial database design is to choose the data types that will capture the information you want in the least amount of space. <\/p>\n

Today, I am exploring binary strings by extending the sample database named [BASIC] that contains a schema named [TRAINING] and table named [BINARY_STRINGS].<\/p>\n

Books online<\/a> describes three data types that are categorized as binary. I created a field for each type in our new table as well as a surrogate key.<\/p>\n

--\r\n-- 1 - Create test table (binary data types)\r\n--\r\n\r\n-- Delete existing table\r\nIF  EXISTS (\r\n    SELECT * FROM sys.objects \r\n    WHERE object_id = OBJECT_ID(N'[TRAINING].[BINARY_STRINGS]') AND \r\n    type in (N'U'))\r\nDROP TABLE [TRAINING].[BINARY_STRINGS]\r\nGO\r\n\r\n-- Create new table\r\nCREATE TABLE [TRAINING].[BINARY_STRINGS]\r\n(\r\n  ID1 TINYINT NOT NULL,       -- 1 BYTE\r\n  BIN1 BINARY(8),              -- 8 BYTES\r\n  BIN2 VARBINARY(4),         -- DATA SIZE (1..4) + 2 BYTES OVERHEAD\r\n  BIN3 VARBINARY(MAX),     -- OUT OF ROW STORAGE (0 .. 2 ^ 31) BYTES\r\n  BIN4 IMAGE\t\t      -- DITTO - DEPRECIATED IN FUTURE RELEASES\r\n);\r\nGO\r\n<\/span><\/pre>\n
<\/P><\/p>\n
Many people use binary fields to store documents such as PDF files.  Due to the size of these files, I am going to make adjustments to increase the data and log files associated with the [BASIC] database.<\/p>\n
--\r\n-- 2 - Alter data & log files settings\r\n--\r\n\r\n-- Select the correct database\r\nUSE BASIC\r\nGO\r\n\r\n-- Switch owner to system admin\r\nALTER AUTHORIZATION ON DATABASE::BASIC TO SA;\r\nGO\r\n\r\n-- Change data size to load pdf\r\nALTER DATABASE BASIC \r\nMODIFY FILE\r\n  (NAME = BASIC_DAT, SIZE = 10 MB, MAXSIZE = 1000 MB, FILEGROWTH = 10 MB);\r\nGO\r\n\r\n-- Change log size to load pdf\r\nALTER DATABASE BASIC \r\nMODIFY FILE\r\n  (NAME = BASIC_DAT, SIZE = 10 MB, MAXSIZE = 1000 MB, FILEGROWTH = 10 MB);\r\nGO\r\n<\/span><\/pre>\n
<\/P><\/p>\n
We should test the newly created table by loading some binary data. <\/p>\n
--\r\n-- 3 - Add one row with some simple binary data\r\n--\r\n\r\nINSERT INTO [TRAINING].[BINARY_STRINGS] VALUES\r\n(44, 0x03A0202F41100819, 0xA02F1019, 0x0, 0x0)\r\nGO\r\n\r\n-- Return the data from the table\r\nSELECT * FROM [TRAINING].[BINARY_STRINGS];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
To make this test more complex, I found a E-book<\/a> version of Arthur Conan Doyle’s  Sherlock Holmes<\/a> adventures.<\/p>\n
--\r\n-- 4 - Update row with Sherlock Holmes Adventures (pdf)\r\n--\r\n\r\n    \r\n-- Update test table with data file\r\nUPDATE [TRAINING].[BINARY_STRINGS]\r\nSET \r\n    BIN3 = (SELECT * FROM OPENROWSET(BULK N'C:\\TEMP\\SHERLOCK-HOLMES.PDF', SINGLE_BLOB) AS FREE_EBOOK),\r\n    BIN4 = (SELECT * FROM OPENROWSET(BULK N'C:\\TEMP\\SHERLOCK-HOLMES.PDF', SINGLE_BLOB) AS FREE_EBOOK)\r\nWHERE ID1 = 44;\r\nGO\r\n\r\n-- Return the data from the table\r\nSELECT * FROM [TRAINING].[BINARY_STRINGS];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
To complete this example, what would a database be if you could not extract the PDF document when it was needed?  We will first need to enable to command shell extended stored procedure using the code below.<\/p>\n
--\r\n-- 5 - Enable the command shell\r\n--\r\n\r\n-- To allow advanced options to be changed.\r\nEXEC sp_configure 'show advanced options', 1\r\nGO\r\n\r\n-- To update the currently configured value for advanced options.\r\nRECONFIGURE\r\nGO\r\n\r\n-- To enable the feature.\r\nEXEC sp_configure 'xp_cmdshell', 1\r\nGO\r\n\r\n-- To update the currently configured value for this feature.\r\nRECONFIGURE\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The only utility that can export binary objects is the bcp.exe program.  The TSQL code below will create and execute a command shell script to extract the document.<\/p>\n
--\r\n-- 6 - Export blob using BCP\r\n--\r\n\r\n-- Local variables\r\nDECLARE @VAR_CMD VARCHAR(1024);\r\nDECLARE @VAR_RETURN INT;\r\n\r\n-- Build the string\r\nSET @VAR_CMD = '';\r\nSET @VAR_CMD = @VAR_CMD + 'BCP \"SELECT [BIN3] FROM [BASIC].[TRAINING].[BINARY_STRINGS] WHERE ID1=44\" ';\r\nSET @VAR_CMD = @VAR_CMD + 'QUERYOUT C:\\TEMP\\BIN3.PDF -T -f C:\\TEMP\\SHERLOCK-HOLMES.FMT -S ' + @@SERVERNAME;\r\n\r\n-- Show the string\r\nPRINT @VAR_CMD;\r\n \r\n-- Execute the command\r\nEXEC @VAR_RETURN = MASTER..xp_cmdshell @VAR_CMD;\r\n\r\n-- Did not work\r\nIF (@VAR_RETURN > 0)\r\nBEGIN\r\n    RAISERROR ('Unable to export the pdf file to os system.  Please check the file system for issues.', 16, 1) WITH LOG;\r\nEND\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The screen shot below shows the execution of the BCP program exporting the binary document to disk.<\/p>\n
\"\"<\/a><\/p>\n
As a database designer, you should always question the components that make up your database.  <\/p>\n
One question that you might have is ‘What is the maximum number of bytes that a row can have?’.  This is important because data is stored in the *.MDF or *.NDF files as pages <\/a>~ 8k. Since a page can only save 8060 bytes, you can figure out how many records can fit on a page and how many bytes are wasted space.<\/p>\n
The following code uses the sys.columns table to count the number of fields and calculate the maximum row size.<\/p>\n
--\r\n-- Maximum row length (num cols, max bytes)\r\nSELECT \r\n    OBJECT_NAME (c.object_id) tablename,\r\n    COUNT (1) nr_columns,\r\n    SUM (c. max_length) maxrowlength\r\nFROM sys.columns AS c\r\nWHERE OBJECT_NAME(c.object_id) = 'DATE_N_TIME'\r\nGROUP BY OBJECT_NAME (c.object_id)\r\nORDER BY OBJECT_NAME (c.object_id);\r\n<\/span><\/pre>\n
<\/P><\/p>\n
\"\"<\/a><\/p>\n
We can see that 5 columns in the table have a maximum record length of 28 bytes.  This information is misleading since it does not account for the out of row data.  It only counts the first three columns.  The [sp_spaceused] stored procedure shows us that 1 index page and many data pages have been allocated for the table.  <\/p>\n
-- Real life numbers (pages\/extents)\r\nEXEC sp_spaceused 'TRAINING.BINARY_STRINGS';\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
We can query the dynamic management view named index physical stats to the an accurate number of data pages in the heap.   A heap is a table without an index.  In our example, there are 1297 pages allocated for the documents. <\/p>\n
-- Valid for SQL Server 2005 +\r\nSELECT s.database_id, s.object_id, s.index_type_desc, s.page_count, s.record_count, s.min_record_size_in_bytes, s.max_record_size_in_bytes \r\nFROM sys.dm_db_index_physical_stats\r\n(DB_ID(N'BASIC'), OBJECT_ID(N'TRAINING.BINARY_STRINGS'), NULL, NULL , 'DETAILED') AS s;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Last but not least, the sp_help stored procedure displays the details of the table.  This includes many different settings that can be choosen as a DDL designer such as computed column, field length, nullabilty, and collation to mention a few.<\/p>\n
-- Display size details of table\r\nEXEC sp_help 'TRAINING.BINARY_STRINGS'; \r\nGO\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
In summary, binary strings can be used to save bit patterns from your application specific integrated circuit or your favorite music file (MP3) in the database.   Next time, I will be going over types classified as other.<\/p>\n","protected":false},"excerpt":{"rendered":"
I am continuing my series of talks on fundamental topics like data types. I am proud to be a United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic unit of any database is a TABLE which consists of COLUMNS. A vital decision during the initial database design is to choose the data types that will capture the information you want in the least amount of space. Today, I am exploring binary…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[349,554,552,63,510,31,12,555,15,556,266,557,265,522,521,28,523,529,29,553,263],"class_list":["post-2724","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-bcp","tag-binary","tag-binary-strings","tag-create-table","tag-data-types","tag-database-developer","tag-free-code","tag-image","tag-john-f-miner-iii","tag-openrowset","tag-reconfigure","tag-single_blob","tag-sp_configure","tag-sp_help","tag-sp_spaceused","tag-sql-server","tag-sys-columns","tag-sys-dm_db_index_physical_stats","tag-tsql","tag-varbinary","tag-xp_cmdshell"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2724","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2724"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2724\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2724"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2724"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2724"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}