SQL Server 2008 Data Compression

SQL Server 2008 introduced a new feature to compress data stored in tables, indexes and indexed views. It basically uses a few different techniques to save space when storing data. You can set compression to NONE, ROW or PAGE (page includes ROW-level compression).

There are a lot of small details that are relevant here. Here is an incomplete list of those details:

• This feature is targeted at Data Warehouses
• It is only available for SQL Server 2008 Enterprise and Developer editions
• You cannot use it if the max row size exceeds 8060 bytes
• You can have different compression settings per table, partition and index
• You cannot combine compression and sparse columns on the same table

See all the interesting details following the links below:

• Creating Compressed Tables and Indexes
  http://msdn.microsoft.com/en-us/library/cc280449.aspx
• Change using ALTER TABLE or ALTER INDEX statements
  http://msdn.microsoft.com/en-us/library/ms190273.aspx
  http://msdn.microsoft.com/en-us/library/ms188388.aspx
• Stored procedure to estimate compression: sp_estimate_data_compression_savings
  http://msdn.microsoft.com/en-us/library/cc280574.aspx
• Storage Engine blog
  http://blogs.msdn.com/sqlserverstorageengine/archive/tags/Data+Compression/default.aspx