In part 1 of this series here , we have discussed how a decimal number can be expressed 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

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.

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’)

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

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