将 Decimal 数据存储为可变长度类型

  • 项目

新建日期： 2006 年 12 月 12 日

decimal 和 numeric 数据类型在磁盘上通常存储为固定长度数据。numeric 数据类型的功能等效于 decimal 数据类型。在 SQL Server 2005 Service Pack 2 (SP2) 和更高版本中，可以使用 vardecimal 存储格式将 decimal 和 numeric 数据类型存储为可变长度列。vardecimal 存储格式仅在 SQL Server 2005 Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

Bb326755.note(zh-cn,SQL.90).gif注意：
Vardecimal 是一种存储格式，而不是数据类型。

vardecimal 存储格式可以大大减少数据的存储大小，但确实增加了少量 CPU 开销。vardecimal 存储格式应用于表级别。也就是说，不能只将表中的某些 decimal 列存储为 vardecimal 存储格式而不将其他列存储为该格式。decimal 数据将保持为本身的数据类型。

对表启用 vardecimal 存储格式时，将采用 vardecimal 存储格式将 decimal 数据存储在数据、索引和日志页中。更改存储格式的操作是离线进行的。要修改的表在操作过程中以独占方式锁定，因而无法进行并发读写访问。

实现 Vardecimal 存储格式

根据列的精度（1 到 38），decimal 值的存储将占用 5 到 17 个字节。当表不使用 vardecimal 存储格式时，对于每个定义的 decimal 列该表中的每个条目会占用相同数量的字节，即使行值为 0、空值或某些可以用较少量字节表示的值（如数字 3）。使用 vardecimal 存储格式存储表时，每行的 decimal 列仅占用容纳所提供数字所需的空间，外加 2 个字节的开销。空间占用结果始终介于 5 到 20 个字节之间。其中包括用于存储值偏移量的 2 个字节开销。但是，会对空值和零做特殊处理，它们仅占用 2 个字节。

如果表不包含任何可变长度列，则每行需要 2 个字节的额外开销来存储可变长度列的数量。如果表已至少包含一个可变长度列，则不需要额外开销。

下表显示了采用常规固定格式存储 decimal 数据所需的字节数，以及采用 vardecimal 格式存储 decimal 数据所需的最大字节数。采用固定格式存储表时，列出的值将用于每行。采用 vardecimal 存储格式存储表时，许多值所要求的字节数将少于列出的字节数。

列精度 原始固定 decimal 大小（字节） 最大 vardecimal 数据区域（字节） 存储偏移量的开销（字节） 占用的最大 vardecimal 存储区（字节）

1-3

5

3

2

5

4-6

5

4

2

6

7-9

5

5

2

7

10-12

9

6

2

8

13-15

9

8

2

10

16-18

9

9

2

11

19

9

10

2

12

20-21

13

10

2

12

22-24

13

11

2

13

25-27

13

13

2

15

28

13

14

2

16

29-30

17

14

2

16

31-33

17

15

2

17

34-36

17

16

2

18

37-38

17

18

2

20

使用 Vardecimal 存储格式

可以使用 vardecimal 存储格式尝试解决下列问题：

  • 磁盘空间不足。
  • 磁盘访问 (I/O) 成为系统性能的瓶颈。
  • 某些数据必须具有较高的精度，即使许多值较小、为空值或是 0（例如数据仓库中的表。表中 decimal 列有多行含 0 或整数值）。

更改表的存储格式要求重新生成该表的存储（聚集索引或堆）。如果表的聚集索引包含 decimal 列，则还必须重新生成所有非聚集索引，因为这些非聚集索引包含聚集键值。如果聚集索引不包含 decimal 列，而非聚集索引包含 decimal 列，则重新生成非聚集索引。如果表是堆（即不包含聚集索引），则必须重新生成所有非聚集索引，以便指向堆中的新行位置。

重新生成表以启用或禁用 vardecimal 存储格式需要的空间可能是原始表总存储空间的两倍多。如果表不包含 decimal 或 numeric 列，则启用 vardecimal 存储格式只是一个元数据操作。重新生成表和索引时将导致大量日志活动。

Vardecimal 存储格式可用于只读表和读/写表。必须在节省的存储空间与每次访问表时转换行存储格式所需的额外 CPU 开销之间进行平衡。此外，写入使用 vardecimal 存储格式的表可能会降低性能，因为页拆分数量有所增加。

Vardecimal 存储格式的限制

存在下列限制：

  • 要求 SQL Server 2005 SP2 或更高版本。
  • Vardecimal 存储格式在以下系统数据库中无法启用：master 数据库、model 数据库、msdb 数据库、tempdb 数据库或 distribution 数据库。当查询对采用 vardecimal 存储格式存储的数据进行排序时，将在处于固定 decimal 状态下的 tempdb 中对该数据进行排序。通常，该数据在 tempdb 中需要的空间明显多于 vardecimal 存储格式源表在源数据库中所占用的空间。
  • Vardecimal 存储格式不能应用于视图、索引视图、XML 索引以及全文索引。不过，构成这些对象基础的表可以使用 vardecimal 存储格式。
  • 内部表（例如元数据表和通知表）不能使用 vardecimal 存储格式。
  • 表值函数不能使用 vardecimal 存储格式。
  • 存储在 vardecimal 存储格式表中的 numeric 列也不能进行加密。
  • 不支持异类分区（即固定 decimal 格式分区和 vardecimal 存储格式分区）。
  • 使用 Transact-SQL SELECT … INTO… 语法通过 vardecimal 存储格式表创建的新表不继承 vardecimal 存储格式。
  • 启用了数据库镜像的数据库不能更改其 vardecimal 存储格式状态。必须删除数据库镜像，才能对数据库启用 vardecimal 存储格式。但是，当对各个表启用或禁用了 vardecimal 存储格式时，不必删除数据库镜像。
  • SQL Server 必须能够保证成功完成所有更新，并且表始终可以返回到固定 decimal 格式。因此，如果额外开销导致现有行超过 8060 字节或导致现有索引值超过 900 字节，则表不能更改为 vardecimal 存储格式。
    Bb326755.note(zh-cn,SQL.90).gif注意：
    Vardecimal 存储格式与可变长度文本 (varchar) 存储之间的不同之处在于使用 SQL Server 可以创建允许超过 8060 字节的行（如果所有可变长度列均具有最大大小）。插入或更新文本数据时，SQL Server 将强制执行 8060 字节限制。使用 SQL Server 不能创建一组超过 8060 字节行限制的 decimal 列。当表格式更改为 vardecimal 存储格式时，将强制执行 8060 字节限制。
  • 使用复制数据库向导的分离和附加方法传输数据库时，如果目标数据库引擎不是 SQL Server 2005 SP2 或更高版本，附加操作将失败。SQL Server 管理对象方法将创建不使用 vardecimal 存储格式的新数据库和表。如果数据库引擎为 SQL Server 2005 SP2 或更高版本，则在传输之后可以将数据库和表更改为 vardecimal 格式。

备份和恢复、数据库镜像、sp_attach_db 以及日志传送

备份和恢复、数据库镜像、sp_attach_db 及日志传送均能正常使用 vardecimal 存储格式；但是，若要包括使用 vardecimal 存储格式的数据库，则每个 SQL Server 实例都必须至少升级至 SQL Server 2005 SP2。例如，无法将启用了 vardecimal 存储格式的数据库的日志备份还原到未启用 vardecimal 存储格式的数据库，无法从启用了 vardecimal 存储格式的数据库镜像到未启用 vardecimal 存储格式的数据库，也无法将启用了 vardecimal 存储格式（从 SQL Server 2005 SP2 起实现的功能）的数据库附加到早期版本的 SQL Server。如果将启用了 vardecimal 存储格式的数据库的完整备份还原到未启用 vardecimal 存储格式的数据库，则未启用 vardecimal 存储格式的数据库将变成启用 vardecimal 存储格式。

当表更改为 vardecimal 存储格式时，日志备份链保持有效，通过应用上一个完整备份和有效的日志链可以还原数据库。若要避免创建无效的备份，必须先将数据库更改为简单恢复模式，然后再修改表以删除 vardecimal 存储格式。在删除任何表的 vardecimal 存储格式之后，应当创建完整的数据库备份。

将 Vardecimal 存储格式用于数据库镜像

下列过程提供了将 vardecimal 存储格式用于数据库镜像的步骤。

将 vardecimal 存储格式用于数据库镜像

  1. 将主体实例和镜像伙伴实例至少升级至 SQL Server 2005 SP2。
  2. 如果当前正在使用数据库镜像，则移除数据库镜像并删除镜像伙伴。有关详细信息，请参阅如何删除数据库镜像 (Transact-SQL)。
  3. 对主体数据库启用 vardecimal 存储格式，并确保主体数据库处于完整恢复模式。
  4. 使用主体数据库的完整备份和日志备份建立数据库镜像。有关详细信息，请参阅如何建立使用 Windows 身份验证的数据库镜像会话 (Transact-SQL)。
  5. 将各个表修改为使用 vardecimal 存储格式。
Bb326755.note(zh-cn,SQL.90).gif注意：
更改各个表的存储格式时，不必删除数据库镜像。

删除 vardecimal 存储格式

  1. 修改主体数据库中的表以删除 vardecimal 存储格式。
  2. 删除数据库镜像。
  3. 将主体数据库设置为简单恢复模式。这会打断日志链。
  4. 对主体数据库禁用 vardecimal 存储格式。
  5. 删除镜像伙伴数据库。
  6. 将主体数据库更改回完整恢复模式。
  7. 备份主体数据库并重新建立数据库镜像。

Vardecimal 存储格式对复制操作的影响

对使用 vardecimal 存储格式的表可以照常执行复制操作，但需注意下列事项：

  • 采用 vardecimal 存储格式存储的 decimal 数据类型在复制过程中将转换为固定 decimal 格式以便传输。不能对 distribution 数据库启用 vardecimal 存储格式。因此，将数据存储在 distribution 数据库的复制表中时，不会采用 vardecimal 存储格式存储数据。在订阅服务器上，通常会应用日志记录。
  • 采用 vardecimal 存储格式的表可以复制到采用固定 decimal 格式的表，反之亦然。
  • 与新订阅有关的建表过程不使用 vardecimal 存储格式创建表。这样便可成功完成复制，而无需考虑数据库引擎 Service Pack 的级别以及订阅数据库的“Vardecimal 存储格式已启用”状态。在创建订阅表之后或通过在应用创建脚本之前修改这些脚本，可以在订阅服务器上对该表启用 vardecimal 存储格式。

下表介绍各种订阅服务器的脚本要求。

订阅服务器 脚本

SQL Server 2000 或 SQL Server 7.0 版

建表脚本无需修改即可使用。

SQL Server 2005，数据库未标记为 vardecimal 存储格式。

建表脚本无需修改即可使用。

SQL Server 2005，数据库标记为 vardecimal 存储格式，但您不希望订阅服务器表启用 vardecimal 存储格式。

建表脚本无需修改即可使用。

SQL Server 2005，数据库标记为 vardecimal 存储格式，您希望 SQL Server 2005 订阅服务器表启用 vardecimal 存储格式。

可以将建表脚本修改为在数据库中启用 vardecimal 存储格式，或启用表的 vardecimal 存储格式。也可以使用下文“启用 Vardecimal 存储格式”一节中介绍的存储过程来启用订阅服务器数据库和订阅服务器表。

其他注意事项

下面列出了使用 vardecimal 存储格式时需记住的其他注意事项：

  • vardecimal 存储格式不会影响大容量导入和导出 (bcp) 操作。
  • DATALENGTH 函数不检测 vardecimal 存储格式，但会返回以固定 decimal 格式存储的字节数。
  • 在极少情况下，vardecimal 存储格式会阻碍 SQL Server 使用固定 decimal 数据的最佳查询计划。
  • vardecimal 存储格式可用于任何数据库兼容级别。
  • 执行 sp_tableoption 时，如果表不包含 decimal 或 numeric 数据类型的列，则表元数据将更改为指示该表使用 vardecimal 存储格式。之后添加新的 decimal 列时，将采用 vardecimal 存储格式存储这些列。在使用 vardecimal 存储格式的表中添加或删除列不需要特殊技术。

启用 Vardecimal 存储格式

启用或更改 vardecimal 存储格式需要下列权限：

  • 在数据库中启用 vardecimal 存储格式需要在服务器上拥有 ALTER DATABASE 权限。
  • 将表更改为 vardecimal 存储格式需要对表拥有 ALTER 权限。

在启用 vardecimal 存储格式之前，应首先验证启用 vardecimal 存储格式时表是否会缩小。当大多数行需要所定义的列精度时，与 vardecimal 存储格式相关联的开销可能会超过所节省的空间，并且可能会导致更大的表。若要在修改表之前估计行大小的减小量，请使用 sp_estimated_rowsize_reduction_for_vardecimal 存储过程。如果决定更改表的存储格式，请先对数据库启用 vardecimal 存储格式，然后再对各个表启用 vardecimal 存储格式。

可以使用存储过程或 SQL Server Management Studio 为 decimal 数据类型启用 vardecimal 存储格式：

  • 运行 sp_db_vardecimal_storage_format 以在数据库中启用 vardecimal 存储格式，然后运行 sp_tableoption 在相应的表中启用 vardecimal 存储格式。
  • 在 Management Studio 中，使用**“数据库属性 - 选项页”**在数据库中启用 vardecimal 存储格式。必须使用 sp_tableoption 将表更改为 vardecimal 存储格式。

识别 Vardecimal 存储格式表

若要确定数据库中的哪些表使用的是 vardecimal 存储格式，请使用 OBJECTPROPERTY 函数并搜索 TableHasVarDecimalStorageFormat 属性。

如果 Production.WorkOrderRouting 表使用的是 vardecimal 存储格式，则下面的示例返回 1；否则返回 0。

USE AdventureWorks ;
GO
SELECT OBJECTPROPERTY(OBJECT_ID('Production.WorkOrderRouting'), 
   'TableHasVarDecimalStorageFormat') ;
GO

以下示例将搜索 AdventureWorks 数据库中使用了 vardecimal 存储格式的所有表。

USE AdventureWorks ;
GO
SELECT name, object_id, type_desc
FROM sys.objects 
 WHERE OBJECTPROPERTY(object_id, 
   N'TableHasVarDecimalStorageFormat') = 1 ;
GO

与删除 Vardecimal 存储格式相关的问题

从表中删除 vardecimal 存储格式之前，必须先采用固定 decimal 格式重新生成该表。这可能会大大增加表所占用的磁盘空间。如果没有足够的可用磁盘空间，则操作会失败。在这种情况下，在禁用 vardecimal 存储格式之前，必须先确保有足够的磁盘空间可供 SQL Server 使用。同时，展开操作也需要临时空间来存储 vardecimal 格式和常规格式的数据。如果可用磁盘空间能够容纳展开之后的展开表，但只是因为缺少临时磁盘空间而无法展开，则可以通过将表的行复制到未展开的新表中来增量展开数据。

若要在修改数据库之后立即从数据库中删除 vardecimal 存储格式，请删除数据库，然后使用在数据库启用 vardecimal 存储格式之前所做的备份还原数据库。

将数据库从 SQL Server 2005 Enterprise、Developer 或 Evaluation edition 移至另一版本或早期版本的 SQL Server 之前，必须先使用所要求的任一版本打开数据库，再删除 vardecimal 存储格式，然后迁移数据库。将包含 vardecimal 存储格式的数据库附加到不符合要求的服务器会失败。

请参阅

概念

使用 decimal、float 和 real 数据

帮助和信息

获取 SQL Server 2005 帮助