Microsoft SQL Server 2012 Bible

Understanding Data Compression

Every IT professional is familiar with data compression, such as zip files and .jpg compression, to name a couple of popular compression technologies.

But SQL Server data compression is specific to the SQL Server storage engine and has a few database-specific requirements. First, there must be zero risk of loss of data fidelity. Second, it must be completely transparent — enabled without any application code changes.

SQL Server data compression isn't like .jpg compression, where you can choose the level of compression and more compression means more data loss. With SQL Server data compression, the data is transparently compressed by the storage engine, and every compressed data page retains every data value when decompressed.

Note

Don't confuse data compression with backup compression — the two technologies are completely independent.

You can compress the following data objects:

• Entire heap
• Entire clustered index
• Entire nonclustered index
• Entire indexed view (specifically, the materialized clustered index of an indexed view)
• Single partition of partitioned table or index

Note

Although indexes can be compressed, they are not automatically compressed with the table's compression type. All objects, including indexes, must be individually, manually enabled for compression.

Following are data compression limitations:

• Heaps or clustered indexes with sparse data may not be compressed.
• File stream data or LOB data is not compressed.
• Tables ...