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.
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
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 ...