SQL 2008 : Table Compression

A quick demo of SQL 2008's table compression.
Table compression can be performed by compressing on a ROW or a PAGE basis.

Step 1 : Decide which method to use by estimating the savings compression will bring.
Their is a handy procedure to do this for you.

-- Estimate for row based compression 

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'ROW'; 
go

-- Estimate for page based compression

EXEC sp_estimate_data_compression_savings 
       @schema_name =  'dbo'  
     , @object_name =  'DummyTestTable' 
     , @index_id =  null 
     , @partition_number =  null 
     , @data_compression =  'PAGE'; 
go

The results


So, the original table size is 66224960 KB

Size when compressed using ROW based compression 54011056 KB (size 82%, saving 18%)
Size when compressed using PAGE based compression 36455952 KB (size 55%, saving 45%)

Step 2 : Compress the table!

ALTER TABLE dbo.DummyTestTable REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

excellent table compression article :
http://www.devx.com/SQL_Server/Article/41171/1763