Data Compression in SQL Server 2008

Data Compression in SQL Server has been made available in SQL Server 2005 with Service Pack 2 where a new storage format for storing decimal and numeric data was introduced. The vardecimal storage format allows decimal and numeric data types to be stored as a variable-length column. This concept has been extended in SQL Server 2008 to all fixed-legth data type.
You can compressing data in the tables and indexes into two levels:
1. Row level
2. Page level
In row level converts all data types to variable-length data types. It also uses no storage space to store NULL values. The more fixed-length data types (such as datetime2,int, decimal, and nchar) that you use in a table, the more likely you are to benefit from row-level compression.

In page level compression includes row-level compression and adds page-level compression using page dictionary and column prefixing. Page dictionary simply introduces pointers between rows in the same page to avoid storing redundant data.
Consider the following:

Row 01: FADI AHMAD
Row 02: SHADI AHAMD
Row 03: FADI AHMAD

If this page used page dictionary, it would look like this:

Row 01: FADI AHMAD
Row 02: SHADI AHAMD
Row 03: 01

So the row 01 pointer to row 03 and this's saving your database storage.

Here an examples for both levels:



Now how to evaluate the estimated space saving by using Data Compression ?
There are tow ways :
1. By using sp_estimate_data_compression_savings system stored proceudre

2. Data Compression Wizard
Do the following steps:
Right click on the "Sales.SalesOrderDetail" table like below:

Then choose Manage Compression .
Secondly choose compression type and click calculate to run the same result of sp_estimate_data_compression_savings or next to implement the data compression on the table.