SQL Server 2008中的数据表压缩功能详细介绍_Sql Server

SQL Server 2005 SP2为我们带来了vardecimal功能，当时针对decimail和numeric数据类型推出了新的存储格式--vardecimal。vardecimal存储格式允许 decimal和numeric数据类型的存储作为一个可变长度列。这项功能使得原来定长的decimal数据在数据文件中以可变长的格式存储，据称这项功能可以为典型的数据仓库节省30%的空间，而SQL Server 2008在这一基础上又进一步增强了数据压缩功能。SQL Server 2008现在支持行压缩和页面压缩两种选项，数据压缩选项可以在以下对象上启用：

1.未创建聚簇索引的表
2.创建聚簇索引的表
3.非聚簇索引(对表设置压缩选项不会影响到该表上的非聚簇索引，因此聚簇索引的压缩需要单独设置)
4.索引视图
5.分区表和分区索引中的单个分区

使用数据压缩的意义：

首先让我们来看看，为什么在存储成本不断降低的今天，微软还要煞费苦心地在SQL Server中实现并且不断改进数据压缩技术呢？

尽管存储成本已经不再是传统意义上的首要考虑因素，但是这并不代表数据库尺寸不是一个问题，因为数据库尺寸除了会影响到存储成本之外，还极大地关联到管理成本和性能问题。

致于为什么会有管理成本的问题？因为数据库需要备份，数据库的尺寸越大，那么备份时间就会越长，当然另外一点就是消耗的备份硬件成本也会随之提高(包括需要的备份介质成本和为了满足备份窗口而需要更高级的备份设备带来的采购成本)，还有一种管理成本就是数据库的维护成本，例如我们经常需要完成的 DBCC任务，数据库尺寸越大，我们就需要更多的时间来完成这些任务。

接着我们再看看性能问题。SQL Server在扫描磁盘读取数据的时候都是按照数据页为单位进行读取的，因此如果一张数据页中包含的数据行数越多，SQL Server在一次数据页IO中获得的数据就会越多，这样也就带来了性能的提升。

最后考虑存储的成本，按照原先SQL Server 2005 SP2中vardecimal的压缩数据为例，30%的空间节省也就意味着30%的存储成本，而按照SQL Server 2008当前放出的测试数据，采用新的数据压缩技术可以达到2X-7X的存储率，再加上如果企业要考虑容灾而增加的存储空间，这样节省的存储硬件成本也将是想当可观的。

使用数据压缩方法：

SQL Server 2008中的压缩选项可以在创建表或索引时通过Option进行设置，例如：

复制代码代码如下:

	CREATE TABLE TestTable (col1 int, col2 varchar(200)) WITH (DATA_COMPRESSION = ROW);

如果需要改变一个分区的压缩选项，则可以用以下语句：

复制代码代码如下:

ALTER TABLE TestTable REBUILD PARTITION = 1 WITH (DATA COMPRESSION = PAGE);

如果需要为分区表的各个分区设置不同的压缩选项，可以使用以下的语句：(SQL Server 2008可以对不同的分区使用不同的压缩选项，这一点对于数据仓库应用是非常重要的，因为数据仓库的事实表通常都会有一个或数个热分区，这些分区中的数据经常需要更新，为了避免数据压缩给这些分区上的数据更新带来额外的处理载荷，可以对这些分区关闭压缩选项)

复制代码代码如下:

	CREATE TABLE PartitionedTable (col1 int, col2 varchar(200))

	ON PS1 (col1)

	WITH (

	DATA_COMPRESSION = ROW ON PARTITIONS(1),

	DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4));

如果是为某个索引设置压缩选项的话，可以使用：

复制代码代码如下:

	

	CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);

如果是修改某个索引的压缩选项，可以使用：

复制代码代码如下:

	ALTER INDEX IX_TestTable_Col1 ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);

SQL Server 2008同时还提供了一个名为sp_estimate_data_compression_savings存储过程帮助DBA估计激活压缩选项后对象尺寸。

使用数据压缩的原理：

对于行压缩，SQL Server 2008采用以下三种方法来节省存储空间：
减少了与记录相关联的元数据开销。此元数据为有关列、列长度和偏移量的信息。在某些情况下，元数据开销可能大于旧的存储格式。

它对于数值类型（例如，integer、decimal和float）和基于数值的类型（例如，datetime和money）使用可变长度存储格式。

它通过使用不存储空字符的可变长度格式来存储定长字符串。
对于页面压缩，SQL Server 2008则是在一张数据页面上依次采用：

1.行压缩
2.前缀压缩
3.字典压缩

使用数据压缩注意事项：

尽管SQL Server 2008的数据压缩功能非常有价值，但是仍然需要注意一些问题：
数据压缩功能仅在企业版和开发版中可用

数据压缩可以让一张数据页存储更多的数据行，但是并不能改变单行数据最长8060字节这一限制。

在一张已经设置了数据压缩的表上创建聚簇索引时，聚簇索引默认继承原表上的压缩选项

在未设置聚簇索引的表上设置页面压缩时，只有以下情况才会获得页面压缩的实际效果：

1.数据使用BULK INSERT语法添加到表中
2.数据使用INSERT INTO ... WITH (TABLOCK)语法添加到表中
3.执行带有页面压缩选项的ALTER TABLE ... REBUILD命令

在未设置聚簇索引的表上更改压缩选项，会导致该表上所有非聚簇索引都需要重建，因为这些非聚簇索引指向的数据行地址已经都发生了改变。

在改变压缩选项时所需要的临时空间大小与创建索引是所需要的空间是一样的，因此对于分区表，我们可以逐个分区设置压缩选项来减少临时空间的需求压力。

由于SQL Server 2008中数据压缩技术其实是SQL Server 2005 SP2中vardecimal技术的一个超集，因此设置了数据压缩后就没有必要保留vardecimal了。当然SQL Server 2008为了保持向后兼容性，在当前版本中仍然保留了vardecimal，但是SQL Server 2008的下一个版本及可能就会弃用vardecimal选项，因此做了这些设置的数据库应该尽早改变到数据压缩设置下。

SQL Server 2008的压缩选项是工作在存储引擎层的，对于SQL Server的其他部件来说这一特性是透明的，因此当我们用BULK LOAD的方式将外面的数据导入SQL Server时，会显著的增加CPU的工作载荷，同时将以压缩的数据表导出到外部文件时，可能会消耗比原来多很多的空间。

下面是其他网友的补充

SQL Server 2008中的数据压缩

SQL Server 2008中引入了数据压缩的功能，允许在表、索引和分区中执行数据压缩。这样不仅可以大大节省磁盘的占用空间，还允许将更多数据页装入内存中，从而降低磁盘IO，提升查询的性能。当然，凡事有利有弊，在启用数据压缩后，数据库服务器就需要额外的CPU资源来进行压缩处理。一般说来，数据库服务器的CPU占用率不会太高，而磁盘IO容易成为瓶颈，所以在大多数情况下对大数据库特别是数据仓库启用该项功能还是利大于弊。

SQL Server 2008的数据压缩分为行压缩和页压缩两种。行压缩主要是通过将固定长度类型存储为可变长度类型来实现，同时还减少了与记录相关联的元数据开销。页压缩在行压缩的基础上又增加了前缀压缩和字典压缩，能获得更大的压缩率。

要启用数据库压缩只需在建表语句后加入WITH (DATA_COMPRESSION = ROW)或是WITH (DATA_COMPRESSION = PAGE)即可。如需将现有的索引修改为启用压缩，可通过ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=ROW)或ALTER INDEX index ON Table REBUILD WITH (DATA_C0MPRESSION=PAGE)实现。

最后提供一段简单的用以判断是否需要压缩数据表的脚本，并自动生成压缩脚本供系统管理员执行。这里用到未公开的存储过程sp_MSforeachtable。在这段脚本中@precommand参数用于执行command指令执行前的SQL命令，建立一张临时表用于保存数据表的信息，@command1参数表示需要执行的SQL命令，对每一张表都利用sp_spaceused存储过程获取表的磁盘占用信息并保存到建立的临时表中，@postcommand参数用于执行command指令后的SQL命令，将之前建立的临时表与系统关联，根据设置的条件（数据表占用空间大于10G）生成数据表压缩脚本。

				?

									exec sp_MSforeachtable

									@precommand=N'

									create table ##(

									id int identity,

									name sysname,

									rows int,

									reserved Nvarchar(50),

									data varchar(50),

									indexdata varchar(50),

									unused varchar(50))',

									@command1=N'insert into ##(name,rows,reserved,data,indexdata,unused) exec sp_spaceused ''?''

									update ## set data=SUBSTRING(data, 1, LEN(data) - 2) where id=scope_identity() AND LEN(data) >=2',

									@postcommand=N'SELECT ''ALTER TABLE '' + TABLENAME + '' REBUILD WITH ( DATA_COMPRESSION = PAGE )'' FROM sys.tables A

									JOIN

									(SELECT C.name + ''.'' + A.name AS TABLENAME, object_id FROM ## A

									JOIN sys.objects B

									ON A.name = B.name

									JOIN sys.schemas C

									ON B.schema_id = C.schema_id

									WHERE CAST(data AS int) > 10000000 AND object_id IN (SELECT object_id FROM sys.tables)) B

									ON A.object_id = B.object_id AND type = ''U'';drop table ##'