Data Compression in SQL Server 2008
In this article, I will explain about how to compress data in SQL Server.
Data Compression is a new feature in Microsoft
SQL Server 2008 which automatically compress data stored in database. It reduces
the storage requirement for your data as well as it improves the IO performance.
SQL Server provides two levels of compression which are explained below.
1. Row Level Compression:
- It is the lower level compression which
performs operations like changing the fixed character strings into variable
- NULL and zero values across all data types
are removed. And these values are not stored in the disk.
- Stores CHAR data types as variable-length
data types. For example, if you have CHAR (100) data type and you store 10
characters as data in it. Then only 10 characters space will be given to
variable and blank characters will not stored.
2. Page Level Compression:
- It is the lower level compression. It
starts with row-level data compression. It uses two types of compression
Prefix compression and Dictionary compression.
- Prefix compression is used for common
values pattern across all rows while Dictionary compression is used for
exact values matches across a page.