SQL Server 2008 Data Compression

SQL Server 2008 offers two different forms of data compression – Row and Page level compression.

Row Compression

Row level compression does not apply any algorithm to compress the data. It changes the physical storage format which is efficient in storing the numeric and fixed length character data types. It minimizes the metadata (column information, length, offset etc) associated with a row. No application changes are required when tables are enabled for compression.

Here is an example

--Create a table

CREATE TABLE TestData

(ID int,DataValue varchar(50))

GO

--Insert data into the table

DECLARE @i INT

SET @i = 1

WHILE (@i <= 100000)

BEGIN

INSERT INTO TestData VALUES (@i, 'value:' + cast(@i AS VARCHAR(10)))

SET @i = @i + 1

END

GO

--Check for the savings if row level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Row'

GO

--Alter the table to apply row level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = ROW)

Page Compression

This shares the common data between rows in a given page. It uses the both Row Compression and Prefix Compression.

Prefix Compression

For every column the duplicate prefixes are saved in the compressed information headers (CI) which reside after the page header. A reference number is assigned to the prefix and it is replaced on where the prefixes are used.

Dictionary Compression

All the duplicate values in the entire page are stored on the CI. Prefix compression is applicable to columns where as dictionary compression is applicable to the entire page.

--Check the savings is Page level compression is enabled

sp_estimate_data_compression_savings 'dbo','TestData',NULL,NULL,'Page'

GO

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page)

Compressing a table is a CPU intensive operation. There is an optional parameter called maxdop which can be used to specify the max number of processors we want the rebuild to use.

--Alter the table to apply page level compression

ALTER TABLE TestData REBUILD

WITH (DATA_COMPRESSION = Page, MAXDOP = 4)

For more information here

http://blogs.msdn.com/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

This article explains the compression ratios with real world databases

http://sqlblog.com/blogs/linchi_shea/archive/2008/05/11/sql-server-2008-page-compression-compression-ratios-from-real-world-databases.aspx