表和索引的行压缩和页压缩
SQL Server 2008 支持表和索引的行压缩和页压缩。可以为以下数据库对象配置数据压缩:
- 存储为堆的整个表。
- 存储为聚集索引的整个表。
- 整个非聚集索引。
- 整个索引视图。
- 对于已分区表和已分区索引,可为每个分区配置压缩选项,且对象的各个分区的压缩设置不必相同。
表的压缩设置不自动应用于它的非聚集索引。必须单独设置每个索引。压缩功能不可用于系统表。如果表和索引是使用 CREATE TABLE 和 CREATE INDEX 语句创建的,则可以压缩。若要更改表、索引或分区的压缩状态,请使用 ALTER TABLE 或 ALTER INDEX 语句。
估算压缩后的节省量
若要确定更改压缩状态对表或索引的影响,可使用 sp_estimate_data_compression_savings 存储过程。sp_estimate_data_compression_savings 存储过程仅在 SQL Server 的支持数据压缩的版本中可用。
启动数据压缩向导
-
在对象资源管理器中,右键单击一个表、索引或索引视图,指向“存储”,然后单击“压缩”。
监视压缩
若要监视 SQL Server 的整个实例的压缩,请使用 SQL Server Access Methods 对象的 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 |
微软帮助链接: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_1devconc/html/5f33e686-e115-4687-bd39-a00c48646513.htm