SQL Server 2008新特性－数据压缩

数据压缩（Data Compression）是SQL Server 2008引入的新特性之一，这项技术使数据更有效的保存并且极大减少了存储开销，同时也带来了性能上的显著提高，尤其是对需要大量磁盘I/O的数据仓库。
 
注：其实在SQL Server 2005 SP2版本中，已经引入了 vardecimal ，它使用变长的格式存储定长的decimal和numberic数据。
 
SQL Server 2008支持两种数据压缩：行压缩（Row Compression）和页面压缩（Page Compression）。
数据压缩仅在企业版和开发版中可用。
 
行压缩将定长的字段以变长的物理空间来存储，从而节省了存储空间。
例如，定长的字段char（100）中存储“SQL Server 2008”只需要15字符的长度，而非100字符，从而节约了85%的空间，另外，对于0和null，将不占用存储空间。
行压缩下各类型数据的空间占用：
数据类型
是否影响
细节
tinyint
No
1 byte is the minimum storage needed.
smallint
Yes
If the value fits in 1 byte, only 1 byte will be used.
int
Yes
Uses only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte.
bigint
Yes
Uses only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte.
decimal
Yes
This storage is exactly same as the vardecimal storage format. For more information, see Storing Decimal Data As Variable Length.
numeric
Yes
This storage is exactly same as the vardecimal storage format. For more information, see Storing Decimal Data As Variable Length.
bit
Yes
The metadata overhead brings this to 4 bits.
smallmoney
Yes
Uses the integer data representation by using a 4-byte integer. Currency value is multiplied by 10000 and the resulting integer value is stored by removing any digits after the decimal point. This type has a storage optimization similar to that for integer types.
money
Yes
Uses the integer data representation by using an 8-byte integer. Currency value is multiplied by 10000 and the resulting integer value is stored by removing any digits after the decimal point. This type has a larger range than smallmoney. This type