{"id":2666,"date":"2012-09-01T01:13:58","date_gmt":"2012-09-01T01:13:58","guid":{"rendered":"http:\/\/craftydba.com\/?p=2666"},"modified":"2012-12-23T23:51:06","modified_gmt":"2012-12-23T23:51:06","slug":"basic-training-data-types-part-5","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2666","title":{"rendered":"Basic Training – Data Types – Part 5"},"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 date and time fields.<\/p>\n

I am extenting the sample database named [BASIC] that contains a sample schema named [TRAINING]. The snippet below creates a sample table named [DATE_N_TIME] that contains one or more fields for each data type. <\/p>\n

Books online<\/a> describes six data types that are categorized as date, time or the combination of both. I created a field for each type in our new table.<\/p>\n

--\r\n-- Create test tables (various date\/time 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].[DATE_N_TIME]') AND \r\n    type in (N'U'))\r\nDROP TABLE [TRAINING].[DATE_N_TIME]\r\nGO\r\n\r\n-- Create new table\r\nCREATE TABLE [TRAINING].[DATE_N_TIME]\r\n(\r\n  DT1 DATE,                -- 3 BYTES\r\n  DT2 SMALLDATETIME,       -- 4 BYTES\r\n  DT3 DATETIME,            -- 8 BYTES\r\n  DT4 DATETIME2,           -- VARIES BY PRECISIONS - BETWEEN 6 & 8 BYTES\r\n  DT5 TIME,                -- 5 BYTES\r\n  DT6 DATETIMEOFFSET(7)    -- 10 BYTES\r\n);\r\nGO\r\n<\/span><\/pre>\n
<\/P><\/p>\n
We should test the new created table with values that show the minimum and maximum data points that can be stored.<\/p>\n
-- Insert lower range min values\r\nINSERT INTO [TRAINING].[DATE_N_TIME]\r\nVALUES (\r\n'0001-01-01',\r\n'1900-01-01 00:00:00',\r\n'1753-01-01 00:00:00.000',\r\n'0001-01-01 00:00:00.0000000',\r\n'00:00:00.0000000',\r\n'1900-01-01 00:00:00+00:00'\r\n);\r\nGO\r\n\r\n-- Insert upper range max values\r\nINSERT INTO [TRAINING].[DATE_N_TIME]\r\nVALUES (\r\n'9999-12-31',\r\n'2079-06-06 23:58:59',\r\n'9999-12-31 23:59:59.997',\r\n'9999-12-31 23:59:59.9999999',\r\n'23:59:59.9999999',\r\n'9999-12-30 23:59:59.9999999+00:00'\r\n);\r\nGO\r\n\r\n-- Return the data from the table\r\nSELECT * FROM [TRAINING].[DATE_N_TIME];\r\nGO\r\n<\/span><\/pre>\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 6 columns in the table have a maximum record length of 38 bytes and 212 records will fit into one page.  This leaves 4 bytes of wasted space on each data page.  The sp_spaceused stored procedure shows us that 1 data and 1 index page has been allocated for the table.  This is called a mixed extent.<\/p>\n
-- Real life numbers (pages\/extents)\r\nEXEC sp_spaceused 'TRAINING.DATE_N_TIME';\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
\r\n-- Display size details of table\r\nEXEC sp_help 'TRAINING.DATE_N_TIME';\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
In summary, when designing a table to use exact date time types, choose the data type that will allow the storage of the information in the least amount of space.  Most people choose DATETIME (8 bytes) as the default instead of a SMALLDATETIME (4 bytes).  Selecting the correct data type can amount in savings of up to 50%.  Next time, I will be going over types classified as binary strings.<\/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 date…<\/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":[63,510,31,546,545,548,549,551,12,15,524,547,522,521,28,523,550,29],"class_list":["post-2666","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-create-table","tag-data-types","tag-database-developer","tag-date","tag-date-time","tag-datetime","tag-datetime2","tag-datetimeoffset","tag-free-code","tag-john-f-miner-iii","tag-object_name","tag-smalldatetime","tag-sp_help","tag-sp_spaceused","tag-sql-server","tag-sys-columns","tag-time","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2666","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=2666"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2666\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2666"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2666"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2666"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}