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

image

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.

clip_image004

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

Estimate Row Level compression saving

clip_image006

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

image

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

clip_image010

Estimate Page Level compression saving

clip_image012

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.

Advertisements

4 thoughts on “Data Compression in SQL Server 2008

  1. Thanks 🙂 @Suprotim sir SQL Server 2008 R2 Enterprise edition supports Data Compression @Pinal sir : Thank you but I am more of .net guy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s