Basic Training – Data Types – Part 2 – Ramblings of a Crafty DBA

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.

<span style="color: #008000; font-size: small;">--
-- 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] 
</span>

--

-- 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]

-- 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.

<span style="color: #008000; font-size: small;">-- 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] 
</span>

-- Insert positive data using exponent of natural logarithms