Data Compression IN SQL Server 2008

 

In this article I am trying to illustrate the concepts of Database Compression on SQL Server 2008. It is only the concepts behind the compression only. For details level of learning you need to follow the MSDN or any MS Notes.

 

Where we can do the Data Compression:

 

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

It takes

 

1.    SQL Server 2008 Enterprise Edition

2.    SQL Server 2008 Developer Editions

 

What is the purpose of Data Compression?

 

The data compression has 2 purposes

1.    It reduces the disk usages by decreasing the size of the database.

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

 

1.    PAGE Compression

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

 

A.    Prefix compression

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

 

 

Posted by: MR. JOYDEEP DAS