4.7.8 用数据压缩减少磁盘空间使用
SQL Server 2008 企业版和开发版引入了表、索引和相应分区的行级和页级压缩。
行压缩为数值数据类型（例如，int、bigint和decimal）和固定长度类型（比如money和datetime）使用可变长度的存储。行压缩也为定长字符串使用可变长度存储格式，且不存储尾部的空字符、NULL和0值。
页压缩包含行压缩，以及前缀压缩和字典压缩。前缀压缩包括在行的列中存储多次的列前缀值的存储，并引用单个的值替换掉多余的前缀。字典压缩发生在前缀压缩后，首先查找数据页上任意位置的重复数据值（不只是前缀），然后使用指向单个值的指针替换多余的重复值。
提示 第5章将讲解如何使用CREATE INDEX和ALTER INDEX为非聚集索引启用压缩。
这个技巧将展示如何使用CREATE TABLE和ALTER TABLE来启用行压缩与页压缩。在{dy}个示例中，将为新表启用行压缩。指定DATA_COMPRESSION表选项并从NONE、ROW或PAGE中选择其一来完成它。
- CREATE TABLE dbo.ArchiveJobPosting
- (JobPostingID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
- CandidateID int NOT NULL,
- JobDESC char(2000) NOT NULL
- )
- WITH (DATA_COMPRESSION = ROW)
执行ALTER TABLE...REBUILD WITH来重新配置既有表的压缩选项。例如，如下命令关闭了我刚刚创建的表的压缩选项：
- ALTER TABLE dbo.ArchiveJobPosting
- REBUILD WITH
- (DATA_COMPRESSION = NONE)
下一步，将向表中添加垃圾数据，演示压缩带来的好处。如下的查询插入一行，为CandidateID选择一个随机整型值，为JobDESC重复字母"a"50次。GO命令后跟100000的意思是INSERT将执行100 000次，从而100 000条新行插入到这个表中（如果你自己测试 SQL Server实例，执行它可能会花费几分钟时间）：
- INSERT dbo.ArchiveJobPosting
- (CandidateID, JobDESC)
- VALUES (CAST(RAND() * 10 as int),
- REPLICATE('a',50))
- GO 100000
现在数据添加好了，可以执行sp_estimate_data_compression_saving系统存储过程来估计当使用行压缩或页压缩时可以节省多少磁盘空间。sp_estimate_data_compression_savings存储过程有5个参数：要压缩的表的架构名称、对象名称、索引ID、分区号和数据压缩方式（NONE、ROW或PAGE）。在如下的示例中，先检查使用行压缩可以节省多少空间：
- EXEC sys.sp_estimate_data_compression_savings
- @schema_name = 'dbo',
- @object_name = 'ArchiveJobPosting',
- @index_id = NULL,
- @partition_number = NULL,
- @data_compression = 'ROW'
这个查询返回如下信息（为了便于阅读，调整了格式）：
- object_name ArchiveJobPosting
- schema_name dbo
- index_id 1
- partition_number 1
- size_with_current_compression_setting(KB) 200752
- size_with_requested_compression_setting(KB) 6536
- sample_size_with_current_compression_setting(KB) 39776
- sample_size_with_requested_compression_setting(KB) 1296
正如你在存储过程的结果中看到的，对当前的数据集增加的行压缩可以节省194 216KB。样本大小数据基于存储过程将样本数据载入到tempdb的克隆表中，并验证相应的压缩率。
现在将测试，查看使用页级压缩是否带来好处：
- EXEC sys.sp_estimate_data_compression_savings
- @schema_name = 'dbo',
- @object_name = 'ArchiveJobPosting',
- @index_id = NULL,
- @partition_number = NULL,
- @data_compression = 'PAGE'
这个查询返回：
- object_name ArchiveJobPosting
- schema_name dbo
- index_id 1
- partition_number 1
- size_with_current_compression_setting(KB) 200752
- size_with_requested_compression_setting(KB) 1200
- sample_size_with_current_compression_setting(KB) 40144
- sample_size_with_requested_compression_setting(KB) 240
非常肯定，页级压缩比使用行级压缩会带来更大的好处。
警告 作为代价，压缩通常会增加CPU的使用。你必须考虑并测试你当前的应用程序，在磁盘空间和带来的CPU开销之间作出权衡。
然后，继续使用ALTER TABLE来启用表的页级压缩：
- ALTER TABLE dbo.ArchiveJobPosting
- REBUILD WITH
- (DATA_COMPRESSION = PAGE)
也可以在分区级别配置压缩。在下面一组命令中，我将创建新的分区函数和方案，然后将其应用到新表上。表会基于分区使用不同的压缩级别。首先创建分区函数和方案：
- CREATE PARTITION FUNCTION pfn_ArchivePart(int)
- AS RANGE LEFT FOR VALUES (50000, 100000, 150000)
- GO
-
-
- CREATE PARTITION SCHEME psc_ArchivePart
- AS PARTITION pfn_ArchivePart
- TO (hitfg1, hitfg2, hitfg3, hitfg4) ;
- GO
下一步，创建在JobPostingID整型列上引用了分区方案的表。还要指定哪些分区将使用PAGE压缩，哪些分区使用行压缩：
- CREATE TABLE dbo.ArchiveJobPosting_V2
- (JobPostingID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
- CandidateID int NOT NULL,
- JobDESC char(2000) NOT NULL)
- ON psc_ArchivePart(JobPostingID)
- WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 3),
- DATA_COMPRESSION = ROW ON PARTITIONS (4))
如果希望为任一分区更改压缩级别，可以使用ALTER TABLE，如下面所演示的，将分区4从行压缩更改为页压缩：
- ALTER TABLE dbo.ArchiveJobPosting_V2
- REBUILD PARTITION = 4
- WITH (DATA_COMPRESSION = PAGE)
解析
这个技巧演示了如何使用CREATE TABLE和ALTER TABLE为表应用页级和行级压缩。Sql Server 2008企业版和开发版引入了压缩特性，这个特性可以减少数据库表的总的磁盘使用空间。依赖于存储在表中数据的类型，整体压缩率将会有很大的不同。也要注意到，压缩在带来好处的同时，也会带来更大的CPU开销，因此需要在部署到生产运营环境之前进行彻底的测试。
将CREATE TABLE或ALTER TABLE命令与DATA_COMPRESSION子句结合使用就可以启用压缩（我将在第5章演示非聚集索引的压缩）。可以对堆（无聚集的索引）、聚集索引、非聚集索引、索引视图及表或索引指定的分区设置压缩。要验证增加行压缩或页压缩带来的好处，使用sp_estimate_data_compression_ saving系统存储过程，如本技巧中演示的。
引用：
