{"id":2547,"date":"2012-08-16T21:04:07","date_gmt":"2012-08-16T21:04:07","guid":{"rendered":"http:\/\/craftydba.com\/?p=2547"},"modified":"2012-12-23T23:53:56","modified_gmt":"2012-12-23T23:53:56","slug":"basic-training-data-types-part-i","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2547","title":{"rendered":"Basic Training – Data Types – Part 1"},"content":{"rendered":"

\"\"<\/a>Just the other day, I was tasked with redesigning a data warehouse’s star schema<\/a> that grew to over 4 terabytes in size. After completing the project, I realized that if the original designers knew more about storage (data types, data pages, index pages), the explosive growth would have not been so bad. <\/p>\n

I ended up putting the database on a diet of daily table partitions<\/a> and page compression<\/a>. Today, the database is 20% of it’s orginal size.<\/p>\n

In short, I am going to start off a series of talks covering such fundamental topics. I am a proud United States Army Reservist (USAR<\/a>) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING<\/a>.<\/p>\n

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

I am going to talk today about Exact Numerical data types<\/a>. These types can be categorized as integer, money, or decimal with exact precision. No precision is lost during storage like Approximate Numerical data types.<\/p>\n

The first step is to create a sample database named [BASIC] that contains a sample schema named [TRAINING]. The snippet below accomplishes these actions.<\/p>\n

--\r\n-- Create basic database\r\n--\r\n\r\n-- Which database to use.\r\nUSE [master]\r\nGO\r\n\r\n-- Delete existing databases.\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'BASIC')\r\n    DROP DATABASE [BASIC]\r\nGO\r\n\r\n-- Add new databases.\r\nCREATE DATABASE [BASIC] ON\r\nPRIMARY\r\n    ( NAME = N'BASIC_DAT', FILENAME = N'C:\\MSSQL\\DATA\\BASIC.MDF' , \r\n    SIZE = 5MB , MAXSIZE = 20MB , FILEGROWTH = 20%)\r\nLOG ON\r\n    ( NAME = N'BASIC_LOG', FILENAME = N'C:\\MSSQL\\LOG\\BASIC.LDF' , \r\n    SIZE = 1MB , MAXSIZE = 5MB , FILEGROWTH = 512KB );\r\nGO \r\n\r\n\r\n--\r\n-- Create training schema\r\n--\r\n\r\n-- Which database to use.\r\nUSE [BASIC]\r\nGO\r\n\r\n-- Delete existing schema.\r\nIF EXISTS (SELECT * FROM sys.schemas WHERE name = N'TRAINING')\r\nDROP SCHEMA [TRAINING]\r\nGO\r\n\r\n-- Add new schema.\r\nCREATE SCHEMA [TRAINING] AUTHORIZATION [dbo]\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The second step is to create a sample table named [EXACT_NUMERICS] that contains one or more fields for each data type.  Books online<\/a> describes nine data types that are considered exact numerics. Most data types have a range of values that can be stored.  Some data types use precision (total number of digits) and scale (number of decimal points) to vary the range of values.  With this variation comes differences in storage size.<\/p>\n
--\r\n-- Create test tables (exact numerics)\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].[EXACT_NUMERICS]') AND \r\n    type in (N'U'))\r\nDROP TABLE [TRAINING].[EXACT_NUMERICS]\r\nGO\r\n\r\n-- Create new table\r\nCREATE TABLE [TRAINING].[EXACT_NUMERICS]\r\n(\r\n  EN1 BIT,\t       -- 8 BIT FIELDS = 1 BYTE  \r\n  EN2 TINYINT,         -- 1 BYTE\r\n  EN3 SMALLINT,        -- 2 BYTES\r\n  EN4 INT,             -- 4 BYTES\r\n  EN5 BIGINT,          -- 8 BYTES  \r\n  EN6 SMALLMONEY,      -- 4 BYTES\r\n  EN7 MONEY,           -- 8 BYTES\r\n  EN8 DECIMAL(8, 3),   -- DEPENDS ON (P, S)\r\n  EN9 NUMERIC(18, 5),  -- DEPENDS ON (P, S)\r\n  EN10 NUMERIC(38, 0)  -- DEPENDS ON (P, S)\r\n);\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
The third step is to load the 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].[EXACT_NUMERICS] \r\nVALUES (\r\n0, \r\n0,\r\n-32768,\r\n-2147483648,\r\n-9223372036854775808,\r\n-214748.3648,\r\n-922337203685477.5808,\r\n-99999.999,\r\n-9999999999999.99999,\r\n-999999999999999999999999999999999999\r\n);\r\nGO\r\n\r\n-- Insert upper range max values\r\nINSERT INTO [TRAINING].[EXACT_NUMERICS] \r\nVALUES (\r\n1, \r\n255,\r\n32767,\r\n2147483647,\r\n9223372036854775807,\r\n214748.3647,\r\n922337203685477.5807,\r\n99999.999,\r\n9999999999999.99999,\r\n999999999999999999999999999999999999\r\n);\r\nGO\r\n\r\n-- Return the data from the table\r\nSELECT * FROM [TRAINING].[EXACT_NUMERICS] \r\nGO\r\n<\/span><\/pre>\n<\/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<\/a> table to count the number of fields and calculate the maximum row size.<\/p>\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) = 'EXACT_NUMERICS'\r\nGROUP BY OBJECT_NAME (c.object_id)\r\nORDER BY OBJECT_NAME (c.object_id);\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
We can see that 10 columns in the table have a maximum record length of 59 bytes and 136 records will fit into one page.  This leaves 36 bytes of wasted space on each data page.  The sp_spaceused<\/a> 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.EXACT_NUMERICS';\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Last but not least, the sp_help<\/a> 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.EXACT_NUMERICS';\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
In summary, when designing a table to use exact numerical data types, choose the data type that will allow the storage of the information in the least amount of space.  Most people choose INT (4 bytes) or MONEY (8 bytes) as the default.  Selecting the correct data type can amount in savings of up to 50%.  Next time, I will be going over approximate numerical types.<\/p>\n","protected":false},"excerpt":{"rendered":"
Just the other day, I was tasked with redesigning a data warehouse’s star schema that grew to over 4 terabytes in size. After completing the project, I realized that if the original designers knew more about storage (data types, data pages, index pages), the explosive growth would have not been so bad. I ended up putting the database on a diet of daily table partitions and page compression. Today, the database is 20% of it’s orginal size. In short, I am going to start off a series of talks covering…<\/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":[516,512,63,510,394,31,519,511,12,515,15,518,520,524,514,517,522,521,28,523,513,29],"class_list":["post-2547","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-bigint","tag-bit","tag-create-table","tag-data-types","tag-database","tag-database-developer","tag-decimal","tag-exact-numerics","tag-free-code","tag-int","tag-john-f-miner-iii","tag-money","tag-numeric","tag-object_name","tag-smallint","tag-smallmoney","tag-sp_help","tag-sp_spaceused","tag-sql-server","tag-sys-columns","tag-tinyint","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2547","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=2547"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2547\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}