O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required