SQL Server 如何在内部存储十进制类型的值? [英] How does SQL Server store decimal type values internally?

问题描述

在 SQL Server 中，您可以使用 FLOAT 或 REAL 来存储浮点值，其存储格式由 IEEE 754 标准定义.对于定点值，我们可以使用 DECIMAL 类型(它有一个同义词 NUMERIC).但是我不太确定 SQL Server 如何在内部存储 DECIMAL 值.例如，如果我定义一个表并插入这样的行:

In SQL Server you can use FLOAT or REAL to store floating point values the storage format of which is cleared defined by the IEEE 754 standard. For fixed point values we can use DECIMAL type (which has a synonym NUMERIC). However I'm not pretty sure how SQL Server store DECIMAL values internally. For example, if I define a table and insert a row like this:

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

当我使用 DBCC PAGE 命令检查 SQL Server 如何存储数字 5.7456 时，我得到了这个:

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

01 70 E0 00 00

这个十六进制字符串应该使用小端.我无法弄清楚 SQL Server 如何将 5.7456 转换为 01 70 E0 00 00 以及它如何决定整数部分的字节数和小数部分的字节数.有人可以帮忙吗?

This hex string should use little endian. I can't figure out how SQL Server turns 5.7456 into 01 70 E0 00 00 and how it decides how many bytes are for the integral part and how many bytes are for the decimal parts. Can anyone help?

顺便说一句，我看过SQL Server 2012 Internals"一书.有一章专门介绍数据类型存储.不过好像书中没有提到DECIMAL类型的存储.

BTW, I've checked the book "SQL Server 2012 Internals". There is a chapter dedicated to data type storage. But it seems DECIMAL type storage is not mentioned in the book.

推荐答案

Martin's Smith 的评论为您提供了线索.SQL Server 不使用 BCD.它将数据存储为整数，没有小数位(它可以这样做，因为小数位存储在列的元数据中).所以 5.7456 存储为 57456，或 0xE070.在 SQL 臭名昭著的字节交换之后，它被转换为 70 E0 00 00.

Martin's Smith comment gives you the clue. SQL Server does not use BCD. It stores the data as a whole number, without the decimal place (which it can do because the decimal place is stored in the metadata for the column). So 5.7456 is stored as 57456, or 0xE070. After SQL's infamous byte swapping this is transformed to 70 E0 00 00.

开头的 01 是符号.01 用于正数；00 表示否定.

The leading 01 is the sign. 01 is used for positive numbers; 00 for negatives.

(但是，我必须问 - 你为什么需要这个?在典型的使用中，你永远不需要打扰 SQL Server 的内部结构)

(However, I must ask - why do you need this? In typical use, you should never need to bother with SQL Server's internals)

这篇关于SQL Server 如何在内部存储十进制类型的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！