Data Compression in SQL Server 2008

SQL Server 2008 automatically compress data stored in database. SQL server does Lossless data compression.

SQL server uses Dictionary based compression algorithm.

Row Level Data Compression

For Row level Data Compression SQL Server does not use explicitly any standard compression algorithm. It works on very simple algorithm. Say,

1. You created a column of CHAR(50)

2. Normally SQL server requires 50 bytes regardless of the actual byte needed by your Data.

3. If you are storing “DEBUG MODE” in that column then you really need 10 bytes of storage.

So in Row Level Data Compression, rather than fixed format data storage, SQL Server stores data in variable format.

We need to say at time of table creation that DATA_COMPRESSION = ROW

Estimate Row Level compression saving

In above query dbo is name of the schema and TempTable is name of the table and ROW parameter says to estimate Row level estimation.

Page Level Data Compression

In SQL server 2005 page level compression was done by minimizing data redundancy but in SQL Server 2008 it is performed by

1. Reducing Data Redundancy

2. Lossless Data Compression algorithm

3. Column Prefix compression

With column prefix compression first SQL Server identifies repeated byte sequence in beginning of column in all rows on the page. If same column is having same byte pattern in more than one row then SQL server stores the byte pattern once and replaces the other byte patterns with the pointer.

SQL Server creates Dictionary per page and stores repeating vales of the page in the dictionary and performs the compression on Dictionary.

Compression Saving is directly proportional to repeated byte patterns

Estimate Page Level compression saving

In above query dbo is name of the schema and TempTable1 is name of the table and PAGE parameter says to estimate Row level estimation.