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
https://msdn.microsoft.com/en-us/library/cc280449.aspx - Change using ALTER TABLE or ALTER INDEX statements
https://msdn.microsoft.com/en-us/library/ms190273.aspx
https://msdn.microsoft.com/en-us/library/ms188388.aspx - Stored procedure to estimate compression: sp_estimate_data_compression_savings
https://msdn.microsoft.com/en-us/library/cc280574.aspx - Storage Engine blog
https://blogs.msdn.com/sqlserverstorageengine/archive/tags/Data+Compression/default.aspx