主页 / dba / 问题 / 184288
Accepted
xr280xr
Asked: 2017-08-25 10:24:24 +0800 CST

为什么无论精度如何，小数点的数据长度都是5？

  • 772

我试图更好地理解 SQL 中的数字类型，并且已经读到十进制类型总是需要 17 个字节。但是，MS Docs列出了一个表，指示使用的空间量取决于小数点的精度。所以我尝试使用该datalength功能对其进行测试。

create table tbl_TestDec(dec1 decimal(19,4), dec2 decimal(20,4), dec3 decimal(9,4))

insert into tbl_TestDec
select 1, 1, 1

select datalength(dec1), datalength(dec2), datalength(dec3) from tbl_TestDec

这输出：

5   5   5

我期待要么9 13 5要么17 17 17。我正在使用 SQL Server 2005。所有小数都是 vardecimal 还是我误解了 datalength 函数？

sql-server sql-server-2005

2 个回答

  1. DATALENGTH返回传递给它的值的内部表示的大小（以字节为单位）。

    它不会返回所需的存储大小（这可能取决于您存储它的位置），也不会返回可能存储在该类型中的最大数据大小。

    例如（dbfiddle 演示）：

    SELECT
    N.d, 
    AsBinary = CONVERT(varbinary(20), N.d),
    DataLengthVarBin = DATALENGTH(CONVERT(varbinary(20), N.d)),
    DataLengthDec38 = DATALENGTH(N.d) 
FROM 
(
    SELECT 
        CONVERT(
            decimal(38, 38), 
            '0.' + 
            REPLICATE('0', 38 - SV.number) + 
            REPLICATE('1', SV.number))
    FROM master.dbo.spt_values AS SV
    WHERE [SV].[type] = N'P'
    AND SV.number BETWEEN 1 AND 38
) AS N (d);

    给出：

    +----------------------------------------+----- --------------------------------------+------------ ------+----------------+
| d | 作为二进制 | 数据长度变量 数据长度Dec38 |
+----------------------------------------+----- --------------------------------------+------------ ------+----------------+
| 0.00000000000000000000000000000000000001 | 0x2626000101000000 | 8 | 5 |
| 0.00000000000000000000000000000000000011 | 0x262600010B000000 | 8 | 5 |
| 0.00000000000000000000000000000000000111 | 0x262600016F000000 | 8 | 5 |
| 0.00000000000000000000000000000000001111 | 0x2626000157040000 | 8 | 5 |
| 0.00000000000000000000000000000000011111 | 0x26260001672B0000 | 8 | 5 |
| 0.00000000000000000000000000000000111111 | 0x2626000107B20100 | 8 | 5 |
| 0.00000000000000000000000000000001111111 | 0x2626000147F41000 | 8 | 5 |
| 0.00000000000000000000000000000011111111 | 0x26260001C78AA900 | 8 | 5 |
| 0.00000000000000000000000000000111111111 | 0x26260001C76B9F06 | 8 | 5 |
| 0.00000000000000000000000000001111111111 | 0x26260001C7353A42 | 8 | 5 |
| 0.00000000000000000000000000011111111111 | 0x26260001C719469602000000 | 12 | 9 |
| 0.00000000000000000000000000111111111111 | 0x26260001C701BDDE19000000 | 12 | 9 |
| 0.00000000000000000000000001111111111111 | 0x26260001C71162B302010000 | 12 | 9 |
| 0.00000000000000000000000011111111111111 | 0x26260001C7B1D4011B0A0000 | 12 | 9 |
| 0.00000000000000000000000111111111111111 | 0x26260001C7F14E120E650000 | 12 | 9 |
| 0.00000000000000000000001111111111111111 | 0x26260001C77115B78CF20300 | 12 | 9 |
| 0.00000000000000000000011111111111111111 | 0x26260001C771D6267F792700 | 12 | 9 |
| 0.00000000000000000000111111111111111111 | 0x26260001C7716084F7BE8A01 | 12 | 9 |
| 0.00000000000000000001111111111111111111 | 0x26260001C771C42BAB756B0F | 12 | 9 |
| 0.00000000000000000011111111111111111111 | 0x26260001C771ACB5AF98329A | 12 | 9 |
| 0.00000000000000000111111111111111111111 | 0x26260001C771BC18DDF6F90506000000 | 16 | 13 |
| 0.00000000000000001111111111111111111111 | 0x26260001C7715CF7A2A4C33B3C000000 | 16 | 13 |
| 0.00000000000000011111111111111111111111 | 0x26260001C7719CA95D6EA4555A020000 | 16 | 13 |
| 0.00000000000000111111111111111111111111 | 0x26260001C7711CA0A84F6C5887170000 | 16 | 13 |
| 0.00000000000001111111111111111111111111 | 0x26260001C7711C41961C3B7449EB0000 | 16 | 13 |
| 0.0000000000001111111111111111111111111 | 0x26260001C7711C8BDE1D4F8ADE300900 | 16 | 13 |
| 0.0000000000011111111111111111111111111 | 0x26260001C7711C6FB12A1767B1E85B00 | 16 | 13 |
| 0.0000000000111111111111111111111111111 | 0x26260001C7711C57EEAAE706EE169703 | 16 | 13 |
| 0.0000000001111111111111111111111111111 | 0x26260001C7711C674FAD0C454CE5E623 | 16 | 13 |
| 0.0000000011111111111111111111111111111 | 0x26260001C7711C071AC57EB2FAF4046701000000 | 20 | 17 |
| 0.0000000111111111111111111111111111111 | 0x26260001C7711C4704B3F3F8CA9131060E000000 | 20 | 17 |
| 0.0000001111111111111111111111111111111 | 0x26260001C7711CC72AFE84B9EDB1EF3D8C000000 | 20 | 17 |
| 0.0000011111111111111111111111111111111 | 0x26260001C7711CC7ABED313F49F35C6B7A050000 | 20 | 17 |
| 0.0000111111111111111111111111111111111 | 0x26260001C7711CC7B548F377DC80A131C8360000 | 20 | 17 |
| 0.0001111111111111111111111111111111111 | 0x26260001C7711CC719D780AF9C084FF0D1230200 | 20 | 17 |
| 0.0011111111111111111111111111111111111 | 0x26260001C7711CC7016708DB1E56166333661500 | 20 | 17 |
| 0.0111111111111111111111111111111111111 | 0x26260001C7711CC71106548E345DDFDE01FED500 | 20 | 17 |
| 0.1111111111111111111111111111111111111 | 0x26260001C7711CC7B13C488F0DA4B9B412EC5B08 | 20 | 17 |
+----------------------------------------+----- --------------------------------------+------------ ------+----------------+
    • 13
  2. Best Answer

    我认为您会在使用 Vardecimal 存储格式减少数据库大小中找到很多关于此主题的有趣信息，但要回答您的问题（引用帖子，突出显示我的）：

    存储大小取决于列的声明精度 和值。例如，如果您有一个精度为 18 的 decimal 列，并且该列的常用值只有 3 位数字，则 SQL Server 仅使用 5 个字节，这比固定格式使用的 9 个字节少了近 50%。但是，如果列的公共值有 18 位，则 SQL Server 使用 11 个字节，这比固定长度格式中的相应大小多 20%。

    尝试以更高的精度插入值，您的结果将会改变。

    另一个注意事项：

    您可以在数据库和表级别启用 Vardecimal 存储。使用 SSMS 对象资源管理器，您可以查看数据库选项和表存储属性以验证是否启用了 Vardecimal 存储。您还可以使用sp_db_vardecimal_storage_format, 注释：

    返回数据库的当前 vardecimal 存储格式状态或为 vardecimal 存储格式启用数据库。从 SQL Server 2008 开始，用户数据库始终处于启用状态。只有在 SQL Server 2005 中才需要为 vardecimal 存储格式启用数据库。

    也就是说，企业专用的 vardecimal 存储在 SQL Server 2008中被弃用，取而代之的是行压缩，这是vardecimal 存储功能的超集。从 SQL Server 2016 SP1 开始，行和页数据压缩在所有版本中都可用，并且不需要数据库配置选项即可使用。

    • 6

相关问题