Data Compression in SQL Server 2008 - Guest Post by Joydeep Das

This is another small guest post on SQL Server submitted by Joydeep Das. In the previous post, he described about "Join Hint in SQL".

In this post, he will guide you to illustrate the concepts of Database Compression on SQL Server 2008. It is the concepts behind the compression only. For details level of learning you need to follow the MSDN or any MS Notes.

Read more to understand the concept from Joydeep and don’t forget to ask your SQL Queries in the comments section.

Know about the Guest Author

Mr. Joydeep Das is a Microsoft Certified Database Administrator (MCDB), currently working with database design and implementation as a Sr. Software Developer in an ERP development company. Connect with him in his blog: http://sqlservernet.blogspot.com for SQL Server related articles.

Where we can do the Data Compression?

Data compression can performs only in SQL Server 2008 and not for all versions. It takes:

SQL Server 2008 Enterprise Edition
SQL Server 2008 Developer Editions

What is the purpose of Data Compression?

The data compression has 2 purposes:

It reduces the disk usages by decreasing the size of the database.
It improves the I/O Performance.

However implementation of data compression takes extra CPU costs.

Type of Compressions

SQL Server provides 2 type of data compression:

ROW Compression
PAGE Compression

ROW compression is the lower level compression which stores the fixed character strings by using variable-length format by not storing the blank characters. NULL and 0 values across all data types are optimized and take no bytes.

PAGE compression is the higher level compression. It is as similar to table partition, index partitions. Page compression uses two types of compression:

Prefix compression
Dictionary compression

Prefix compression works on common values pattern across all rows on the page. It looks for common patterns in the beginning of common value on given column across all rows on the page.

Dictionary compression works on exact values match pattern across all pages. It looks for exact value matches across all the columns and rows on each page.

This simple SQL statement illustrate that the Which Objects and What Compressions is used:

SELECT * FROM sys.partitions WHERE data_compression_desc != 'NONE'

Planning

There is a lot of work involve while planning for compression strategy for example, Estimating the space saving, Application Workload, Workspace requirements, and mainly what to compress.

Disadvantages

One of the biggest disadvantages of Data compression is database with ROW/PAGE compression cannot be restored, attached or used on other editions. I think that the article is quite informative and thanking you to provide time on it.

If you have any queries, don’t hesitate to ask your queries here in the comments section. Joydeep will try to help you with the answer as soon as possible. Please cheer him up to come up with more SQL Server articles and tips.

If you have come this far, it means that you liked what you are reading. Why not reach little more and connect with me directly on Twitter, Facebook, Google+ and LinkedIn. I would love to hear your thoughts and opinions on my articles directly.

Also, don't forget to share your views and/or feedback in the comment section below.