How does SQL Server store decimal values ​​internally?

Question

In SQL Server, you can use FLOAT or REAL to store floating point values ​​whose storage format is cleared, defined by the IEEE 754. For fixed-point values, we can use the DECIMAL type (which has the synonym NUMERIC ). However, I'm not quite sure how SQL Server stores DECIMAL values ​​inside. For example, if I define a table and insert a row as follows:

 IF OBJECT_ID('dbo.test_number_types') IS NOT NULL DROP TABLE dbo.test_number_types; CREATE TABLE dbo.test_number_types ( id INT IDENTITY(1, 1), c1 NUMERIC(5, 4) ) GO INSERT INTO dbo.test_number_types(c1)VALUES(5.7456); 

When I use the DBCC PAGE command to check how SQL Server stores the number 5.7456, I got the following:

 01 70 E0 00 00 

This hexadecimal string should use a little endian. I cannot understand how SQL Server turns into 5.7456 in 01 70 E0 00 00 and how it determines how many bytes for the integer part and how many bytes for decimal parts. Can anyone help?

---

By the way, I checked the book "SQL Server 2012 Internal Databases". There is a chapter on data type storage. But it seems like DECIMAL not mentioned in the book.

Answer 1

Martin Smith's comment gives you the key. SQL Server does not use BCD. It stores the data as an integer without a decimal place (which it can do since the decimal place is stored in the metadata for the column). Therefore, 5.7456 saved as 57456 or 0xE070 . After transcoding the SQL bytes, this translates to 70 E0 00 00 .

Lead 01 is the mark. 01 used for positive numbers; 00 for negatives.

(However, I have to ask - why do you need this? For typical use, you will never have to worry about the internal components of SQL Server)