How to decide the precision and scale of a decimal column – Part 2

In part 1 of this series here , we have discussed how a decimal number can be Imageexpressed in terms of coefficient, exponent, and sign. We have also discussed how the precision and scale mapped to the coefficient and exponent.

Now let us take a look at how the decimal is physically represented in SQL Server. For that purpose I am creating a table and insert some rows to it.

CREATE TABLE DecimalTest
(
ColID VARCHAR(10),
ColDecimal DECIMAL (5, 4)
)
GO

INSERT INTO DecimalTest VALUES (‘ROW1’, 1.2345)
INSERT INTO DecimalTest VALUES (‘ROW2’, -1.2345)

To see how the decimal is represented in a data page; we are going to use two undocumented DBCC commands, IND and PAGE. The DBCC IND will give the details of file number and page number of the table we are interested. DBCC PAGE will show the actual content of the page (I’m not going in details to the various options of IND and PAGE since these are out of scope of the current discussion).

DBCC IND (‘TestDB’, ‘DecimalTest’, -1)
GO
The output of the command is given below

 

Image

We are concerned about the PageType 1 and corresponding to this, the PageFID is 1 and PagePID is 302. This is our page to investigate. Run the below DBCC PAGE command.
DBCC TRACEON(3604);
GO
DBCC PAGE (‘TestDB’, 1, 302, 1)
GO
DBCC TRACEOFF(3604);
GO
The part of the output we are interested is given below. The data inside the blue rectangle is the decimal values for each row. This is represented in hexadecimal format.

Image

Each two digit represents a byte in hexadecimal format. So, the decimal value is stored here in five bytes. The left most first byte is the sign byte. 01 indicates the decimal value is positive and 00 indicates the decimal value is negative. The remaining four bytes (39 30 00 00) are the actual integer value (coefficient). This is a byte-swapped format and so to get the actual hexadecimal representation we need to swap each byte like: 00 00 30 39. When this is converted to decimal equivalent we get 12345. So the first row stores +12345 and second row stores -12345. To find out the actual decimal value stored we need to know the scale. However this is not stored in the data page. This is because the precision and scale is a metadata and can be easily get by querying the system metadata tables.

SELECT c.name, c.precision, c.scale
FROM sys.columns c
INNER JOIN
sys.types t
ON t.user_type_id = c.user_type_id
WHERE
t.name = ‘decimal’
AND c.object_id = OBJECT_ID(N’DecimalTest’)

Image

So the decimal value stored in each row can be calculated by multiplying the value stored in the row by 10-4.

In SQL Server the bytes required to store each decimal value (coefficient) can vary based on the precision. The Books Online summarized this in the following table.

Precision

Storage bytes 

1 – 9

5

10-19

9

20-28

13

29-38

17

 What about decimal variables? Let us check that next.

DECLARE @D1 AS DECIMAL (5, 4)
DECLARE @D2 AS DECIMAL (5, 4)

SET @D1 = 1.2345
SET @D2 = -1.2345

SELECT CAST(@D1 AS VARBINARY) AS PosDecimal
,CAST(@D2 AS VARBINARY) AS NegDecimal

Image

This is very much similar to the row representation in the data page. However the precision and scale is also encoded to the variable data itself as indicated by the blue rectangle.

In the next part we will discuss the arithmetic operations carried out on decimal numbers

Advertisements

Leave a comment

Filed under Basics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s