Data Compression Techniques in SQL Server 2008 R2

This feature was introduced in SQL Server 2008 and it was enhanced in SQL Server 2008 R2. Basically this feature,   

•    Helps to reduce the size of the database
•    Improves the performance of I/O insensitive workload
•    Requires extra CPU resources on database to compress and decompress the data

Why do we need data compression ?

There are 3 main things that we can obtain using data compression,

• Save the cost of disk usage
• Reduce I/O usage
• Reduce memory usage  

Let me explain this,

As an example take table called "Employee Details". Normally this table can appear in different locations in our database environment such as development environment, QA environment, production environment, backup set, mirror server etc. This means if you can reduce the size of the table you can save overall disk space from these locations. 

Another point, if you can reduce the size of the table that mean you need only few number of data pages to store data (less number of data pages compare with without reduce table size). Less number of data pages means SQL server needs only few number of I/Os to get that data from disk to memory (Logical I/O).Finally less number of data pages require only few memory cells to store that retrieved data.

I think you get a brief idea about above mentioned three points.

Type of Data Compression

Basically SQL server supports two type of data compression. These are,

• Row Compression
• Page Compression

In SQL Server 2008 R2 supports Unicode compression and I'm not going to discuss about the Unicode compression in details here.

Row Compression

• Convert fixed data types to a variable data type
• No compression algorithm
• Less compression ratio compared to page compression

Page Compression

• Super set of Row Compression (SQL Server automatically add Row compression before it goes to Page Compression)
• Reduce column redundancy
• Use compression algorithm
• More compression ratio compared to row compression

I think this is the time to move for the demonstration.

Create to three tables (Uncompress , Compress_Row,Compress_Page)

 
 I inserted the dummy data as well.(Same data I inserted for all table).

Executed select query against both 3 tables seperatly. (Execution time also there)

Executed sp_spaceused to get size of the table(Result also there)

Additionally, I 'll show you how to get estimated saving (Result also  there)

sp_estimate_data_compression_savings 'dbo','UnCompress',null,null,'ROW'
GO
sp_estimate_data_compression_savings 'dbo','UnCompress',null,null,'PAGE'

 There are very simple codes to get the an idea about the database compression

I hope to see you again with another interesting topic.