Basic Training – Data Types – Part 2

I am continuing my series of talks on fundamental topics like data types. I am a proud 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. The most important 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, we are reviewing Approximate Numerical data types which can be categorized as real or float numbers with approximate precision. Precision is lost during storage and/or retrieval of these data types.

I am extenting the sample database named [BASIC] that contains a sample schema named [TRAINING]. The snippet below creates a sample table named [APPROXIMATE_NUMERICS] that contains one or more fields for each data type.

Books online describes two data types that are considered approximate numerics. Most data types have a range of values, minimum and maximum, that can be stored. Some data types such as FLOAT can vary precision, the number digits in the mantissa, which is reflected by the total number of bytes to store the information.

--
-- Create test tables (approximate numerics)
--

-- Delete existing table
IF  EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[TRAINING].[APPROXIMATE_NUMERICS]') AND 
    type in (N'U'))
DROP TABLE [TRAINING].[APPROXIMATE_NUMERICS]
GO

-- Create new table
CREATE TABLE [TRAINING].[APPROXIMATE_NUMERICS]
(
  EN1 REAL,		   -- 4 BYTES  
  EN2 FLOAT(25)		   -- 4 or 8 BYTES, DEPENDS ON (N)
);

-- Clear the table 
TRUNCATE TABLE [TRAINING].[APPROXIMATE_NUMERICS] 

The next step is to load the table with values that show various data points that can be stored.

-- Insert positive data using exponent of natural logarithms
INSERT INTO [TRAINING].[APPROXIMATE_NUMERICS] 
VALUES (
0,
0
);

INSERT INTO [TRAINING].[APPROXIMATE_NUMERICS] 
VALUES (
EXP(80),
EXP(120)
);

INSERT INTO [TRAINING].[APPROXIMATE_NUMERICS] 
VALUES (
EXP(88),
EXP(709)
);

-- Insert negative data using exponent of natural logarithms
INSERT INTO [TRAINING].[APPROXIMATE_NUMERICS] 
VALUES (
-EXP(80),
-EXP(120)
);

INSERT INTO [TRAINING].[APPROXIMATE_NUMERICS] 
VALUES (
-EXP(88),
-EXP(709)
);

-- Return the data from the table
SELECT * FROM [TRAINING].[APPROXIMATE_NUMERICS] 

As a database designer, you should always question the components that make up your database. 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 ~ 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.

The following code uses the dynamic management view, sys.dm_db_index_physical_stats, to count the number of fields and calculate the maximum row size.

-- Valid for SQL Server 2005 +
SELECT 
    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 
FROM 
    sys.dm_db_index_physical_stats
    (DB_ID(N'BASIC'), OBJECT_ID(N'TRAINING.APPROXIMATE_NUMERICS'), 
    NULL, NULL , 'DETAILED') AS s;
GO

We can see that 7 rows in the table have a record length of 19 bytes and 424 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.

-- Real life numbers (pages/extents)
EXEC sp_spaceused 'TRAINING.APPROXIMATE_NUMERICS';

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.

-- Display size details of table
EXEC sp_help 'TRAINING.APPROXIMATE_NUMERICS';

In summary, when designing a table to use approximate numerical data types, choose the data type that will allow the storage of the information in the least amount of space. Some people choose FLOAT over REAL as the default data type. Selecting the correct data type can amount in savings of up to 50%. Next time, I will be going over character strings.