SQL Server Data Compression

To reduce database size, we normally do data compression. in addition of saving space, data compression can help for improve performance of I/O work loads because queries need to read fewer pages. However, extra CPU resources needed for compress and decompress data.

There are several compression types available;

• Row Compression
• Page Compression
• Unicode Compression

In SQL server we can run sp_estimate_data_compression_savings stored procedure to estimate amount of savings which we can have from each compression type. 

In here I have used AdventureWorks2012 database for the demonstration. According to results, page compression will save lots of space. However, we must have a proper plan before compress data. Otherwise lots of issues may introduced.