创建压缩表和索引
SQL Server 2008 支持表和索引的行压缩和页压缩。 可以为以下数据库对象配置数据压缩：
存储为堆的整个表。
存储为聚集索引的整个表。
整个非聚集索引。
整个索引视图。
对于已分区表和已分区索引，可为每个分区配置压缩选项，且对象的各个分区的压缩设置不必相同。
表的压缩设置不自动应用于它的非聚集索引。 必须单独设置每个索引。 压缩功能不可用于系统表。 如果表和索引是使用 CREATE TABLE 和 CREATE INDEX 语句创建的，则可以压缩。 若要更改表、索引或分区的压缩状态，请使用 ALTER TABLE 或 ALTER INDEX 语句。
.gif "注意") 注意 |
|---|
如果现有数据的零碎程度很高，则可能需要重新生成索引（而不是使用压缩）来减小索引的大小。 重新生成索引的过程中将应用索引的填充因子，这可能会增加索引的大小。 有关详细信息，请参阅填充因子。 |
使用行压缩和页压缩时的注意事项
使用行压缩和页压缩时，应注意以下事项：
只有在 SQL Server 2008 Enterprise Edition 和 Developer Edition 中才能使用压缩。
通过压缩可在一页上存储更多的行，但不会更改表或索引的最大行大小。
当最大行大小加上压缩开销超过最大行大小 8060 个字节时，不能对表启用压缩功能。 例如，不能压缩具有 c1char(8000) 和 c2char(53) 列的表，因为存在额外的压缩开销。 当使用 vardecimal 存储格式时，会在启用此格式时执行行大小检查。 对于行压缩和页压缩，在最初压缩对象时会执行行大小检查，以后在每插入或修改一行时也都会执行这一检查。 压缩功能要求遵循下面两条规则：
固定长度类型的更新必须总是成功。
禁用数据压缩必须总是成功。 即使已压缩的行可以容纳在页面中（意味着它小于 8060 个字节），SQL Server 也不允许对哪些在未压缩时无法容纳在行中的更新。
当指定分区列表时，可以将各个分区的压缩类型设置为 ROW、PAGE 或 NONE。 如果未指定分区列表，将使用语句中指定的数据压缩属性来设置所有分区。 创建表或索引时，除非指定了其他压缩设置，否则数据压缩将设置为 NONE。 修改表时，除非指定了其他压缩设置，否则将保留现有压缩设置。
如果指定的分区列表或分区超出范围，将生成错误。
非聚集索引不继承表的压缩属性。 若要压缩索引，必须显式设置索引的压缩属性。 默认情况下，在创建索引时，索引的压缩设置将设置为 NONE。
对堆创建聚集索引时，除非指定了另一压缩状态，否则聚集索引会继承该堆的压缩状态。
如果堆配置为页级压缩，则只有在以下情况下，页才会进行页级压缩：
在启用大容量优化的情况下进行大容量数据导入。
数据是使用 INSERT INTO ... WITH (TABLOCK) 语法插入的。
表是通过执行带 PAGE 压缩选项的 ALTER TABLE ... REBUILD 语句重新生成的。
通过 DML 操作被分配到堆中的新页面将不会使用 PAGE 压缩，除非重新生成该堆。 重新生成堆的方法有：删除压缩然后重新应用压缩，或者创建聚集索引然后再删除聚集索引。
若要更改堆的压缩设置，要求对表重新生成所有非聚集索引，以便它们具有指向堆中的新行位置的指针。
可以联机或脱机启用或禁用 ROW 或 PAGE 压缩功能。 当执行联机操作时，对堆启用压缩功能是单线程的。
启用或禁用行压缩或页压缩的磁盘空间要求与创建或重新生成索引时的磁盘空间要求相同。 对于已分区数据，可以通过每次对一个分区启用或禁用压缩功能来减少所需的空间。
若要确定已分区表中分区的压缩状态，请查询 sys.partitions 目录视图的 data_compression 列。
压缩索引时，可以使用行压缩和页压缩来压缩叶级页。 非叶级页不接收页压缩。
由于大小的关系，大值数据类型有时不与普通行数据存储在一起，而是存储在特殊用途的页上。 对于单独存储的数据，数据压缩不可用。
SQL Server 2005 中实现 vardecimal 存储格式的表在升级后会保留该设置。 可以向具有 vardecimal 存储格式的表应用行压缩。 但是，因为行压缩是 vardecimal 存储格式的超集，所以不必保留 vardecimal 存储格式。 将 vardecimal 存储格式与行压缩一起使用时，十进制值不会进一步压缩。 可以向具有 vardecimal 存储格式的表应用页压缩；但是，vardecimal 存储格式列可能不会实现进一步的压缩。
注意SQL Server 2008 支持 vardecimal 存储格式；但是，由于行级压缩可实现同样的目标，因此不推荐使用 vardecimal 存储格式。 后续版本的 Microsoft SQL Server 将删除该功能。 请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。
估算压缩后的节省量
若要确定更改压缩状态将对表或索引有何影响，请使用 sp_estimate_data_compression_savings 存储过程。 只有在支持数据压缩的 SQL Server 版本中，sp_estimate_data_compression_savings 存储过程才可用。
压缩对已分区表和已分区索引的影响
如果是对已分区表和已分区索引使用数据压缩，则应注意以下事项：
拆分范围
如果使用 ALTER PARTITION 语句拆分分区，则两个分区均继承原始分区的数据压缩属性。
合并范围
合并两个分区时，生成的分区将继承目标分区的数据压缩属性。
切换分区
若要切换分区，该分区的数据压缩属性必须与表的压缩属性匹配。
重新生成一个分区或所有分区
可以使用两种语法变体来修改已分区表或已分区索引的压缩：
下面的语法仅重新生成被引用分区：
ALTER TABLE <table_name> REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = <option>)下面的语法通过对未引用的任何分区使用现有压缩设置来重新生成整个表：
ALTER TABLE <table_name> REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>), ... )
已分区索引使用 ALTER INDEX 遵循同样的原则。
删除已分区聚集索引
删除聚集索引时，除非修改了分区方案，否则对应的堆分区将保留其数据压缩设置。 如果分区方案已更改，则所有分区都将重新生成为未压缩状态。 若要删除聚集索引并更改分区方案，需要执行下列步骤：
1. 删除聚集索引。
2. 使用指定压缩选项的 ALTER TABLE ... REBUILD ... 选项来修改表。
若要删除聚集索引，脱机操作的执行速度很快，因为只删除较高级别的聚集索引。 如果联机删除聚集索引，则 SQL Server 必须重新生成堆两次，一次针对步骤 1，一次针对步骤 2。
压缩对复制的影响
如果将数据压缩与复制一起使用，则应注意以下事项：
当快照代理生成初始架构脚本时，新架构将对表及其索引使用相同的压缩设置。 不能仅对表启用压缩，而不对索引启用压缩。
对于事务复制，项目架构选项决定了必须对哪些依赖对象和属性编写脚本。 有关详细信息，请参阅 sp_addarticle。
分发代理在应用脚本时，不对下级订阅服务器进行检查。 如果选择了压缩的复制，则在下级订阅服务器上创建表将会失败。 在混合拓扑中，不启用压缩的复制。
对于合并复制，发布兼容级别优先于架构选项，并决定了将编写脚本的架构对象。 有关兼容级别的详细信息，请参阅在复制拓扑中使用 SQL Server 的多个版本。
在混合拓扑中，如果不是必须支持新的压缩选项，则发布兼容级别应设置为下级订阅服务器版本。 否则，应在创建表后在订阅服务器上压缩表。
下表列出了在复制期间控制压缩的复制设置。
用户意图 |
为表或索引复制分区方案 |
复制压缩设置 |
脚本编写行为 |
|---|---|---|---|
复制分区方案并在该分区上的订阅服务器上启用压缩。 |
正确 |
正确 |
对分区方案和压缩设置均编写脚本。 |
复制分区方案，但不压缩订阅服务器上的数据。 |
正确 |
错误 |
对分区方案编写脚本，但不对分区的压缩设置编写脚本。 |
不复制分区方案，也不压缩订阅服务器上的数据。 |
错误 |
错误 |
不对分区和压缩设置编写脚本。 |
如果发布服务器上的所有分区均压缩，则压缩订阅服务器上的表，但不复制分区方案。 |
错误 |
正确 |
检查是否对所有分区均启用了压缩。 在表级别对压缩编写脚本。 |
压缩对其他 SQL Server 组件的影响
压缩发生在存储引擎中，数据以未压缩状态呈现给 SQL Server 的其他大部分组件。 这决定了其他组件上的压缩效果仅限于以下方面：
大容量导入和导出操作
导出数据时，即使采用本机格式，数据也以未压缩的行格式输出。 这会导致导出的数据文件的大小比源数据要大得多。
导入数据时，如果已对目标表启用压缩，则存储引擎会将数据转换为压缩的行格式。 这样所使用的 CPU 资源会比将数据导入未压缩表时使用的 CPU 资源多。
如果以大容量方式将数据导入具有页压缩设置的堆，则在插入数据时，大容量导入操作会尝试使用页压缩来压缩数据。
压缩对备份和还原没有影响。
压缩对日志传送没有影响。
数据压缩与稀疏列不兼容。 因此，包含稀疏列的表不能压缩，并且稀疏列也不能添加到压缩表中。
启用压缩可以导致查询计划更改，因为数据是用不同的页数和每页不同的行数存储的。
SQL Server Management Studio 通过**“数据压缩向导”**来支持数据压缩。
启动数据压缩向导
- 在对象资源管理器中，右键单击一个表、索引或索引视图，指向**“存储”，然后单击“压缩”**。
监视压缩
若要监视 SQL Server 的整个实例的压缩，请使用 SQL Server, Access Methods Object 的 Page compression attempts/sec 和 Pages compressed/sec 计数器。
若要获取各个分区的页压缩统计信息，请查询 sys.dm_db_index_operational_stats 动态管理函数。
示例
下面的一些示例使用已分区表，并需要具有文件组的数据库。 若要创建具有文件组的数据库，请执行以下语句。
CREATE DATABASE TestDatabase
ON PRIMARY
( NAME = TestDatabase,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'),
FILEGROUP test1fg
( NAME = TestDBFile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile1.mdf'),
FILEGROUP test2fg
( NAME = TestDBFile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile2.ndf'),
FILEGROUP test3fg
( NAME = TestDBFile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile3.ndf'),
FILEGROUP test4fg
( NAME = TestDBFile4,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDBFile4.ndf') ;
GO
若要切换到新数据库，请执行以下操作：
USE TestDatabase
GO
A. 创建使用行压缩的表
下面的示例创建一个表并将压缩设置为 ROW。
CREATE TABLE T1
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = ROW);
GO
B. 创建使用页压缩的表
下面的示例创建一个表并将压缩设置为 PAGE。
CREATE TABLE T2
(c1 int, c2 nvarchar(50) )
WITH (DATA_COMPRESSION = PAGE);
GO
C. 对已分区表设置 DATA_COMPRESSION 选项
下例使用 TestDatabase 表，该表是通过使用本部分前面提供的代码创建的。 该示例创建一个分区函数和方案，然后创建一个已分区表并为该表的分区指定压缩选项。 在本示例中，分区 1 配置为 ROW 压缩，余下的分区配置为 PAGE 压缩。
若要创建分区函数，请执行以下操作：
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
若要创建分区方案，请执行以下操作：
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
若要创建具有已压缩分区的已分区表，请执行以下操作：
CREATE TABLE PartitionTable1
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
);
GO
D. 对已分区表设置 DATA_COMPRESSION 选项
下面的示例使用示例 C 中使用的数据库。该示例使用用于非连续分区的语法来创建表。
CREATE TABLE PartitionTable2
(col1 int, col2 varchar(max))
ON myRangePS1 (col1)
WITH
(
DATA_COMPRESSION = ROW ON PARTITIONS (1,3),
DATA_COMPRESSION = NONE ON PARTITIONS (2,4)
);
GO
E. 修改表以更改压缩
下面的示例更改在示例 A 中创建的未分区表的压缩。
ALTER TABLE T1
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
F. 修改已分区表中的一个分区的压缩
下面的示例更改在示例 C 中创建的已分区表的压缩。REBUILD PARTITION = 1 语法仅仅导致重新生成编号为 1 的分区。
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO
使用下面的语法的同一操作则会导致重新生成表中的所有分区。
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
G. 修改已分区表中的多个分区的压缩
REBUILD PARTITION = ... 语法可以仅重新生成一个分区。 若要重新生成多个分区，必须执行多个语句，或者执行下面的示例以重新生成所有分区（对未指定的分区使用当前的压缩设置）。
ALTER TABLE PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO
H. 修改索引的压缩
下面的示例使用在示例 A 中创建的表，并对列 C2 创建一个索引。
CREATE NONCLUSTERED INDEX IX_INDEX_1
ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
执行下面的代码，将索引改为页压缩：
ALTER INDEX IX_INDEX_1
ON T1
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
I. 修改已分区索引中的单个分区的压缩
下面的示例对一个已分区表创建索引，该表对索引的所有分区均使用行压缩。
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO
若要创建对不同的分区使用不同的压缩设置的索引，应使用 ON PARTITIONS 语法。 下面的示例对一个已分区表创建索引，该分区表在索引的分区 1 上使用页压缩，在索引的分区 2 至 4 上使用页压缩。
CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;
GO
下面的示例更改已分区索引的压缩。
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;
GO
J. 修改已分区索引中的多个分区的压缩
REBUILD PARTITION = ... 语法可以仅重新生成一个分区。 若要重新生成多个分区，必须执行多个语句，或者执行下面的示例以重新生成所有分区（对未指定的分区使用当前的压缩设置）。
ALTER INDEX IX_PartTab2Col1 ON PartitionTable1
REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)
) ;
GO