Data compression in SQL 2008 is best for data warehouses, where they tend to be very IO constrained and can take a wee hit on CPU and latency.

So how much space will you save with data compression? Well I just took a sample table from our SCOM data warehouse and applied compression. Results are below. The total size went from 1.7 Gb to about 740 MB. Not bad  for one table and its supporting NC indexes.

In total the data warehouse went from 17GB to 10 GB when all the tables and indexes were compressed with page compression.

How long does it take to compress? Well on a two core laptop and a modest disk drive the 17GB database took 30 minutes.

Which Compression is better page or row ? It depends – while page compression seems to always give better compression, if your query only wants one row in a page the whole page needs to be uncompressed, so you’ll take a cpu and latency hit. If you are doing a large table scan then page compression should work great.

How can you compress and entire database at once ? AFAIK you have to compress each table and index separately. Here is a quick script to generate code to compress an entire database in one go. Just don’t try this on your 3TB data warehouse in one go…

select 'ALTER ' + case when si.type =1 then 'INDEX [' + si.name + '] ON ' else 'TABLE ' END +  ' [' + s.name + '].[' + o.name + '] REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=PAGE); ' 
from sys.indexes  si
inner join sys.objects o on o.object_id =si.object_id
inner join sys.schemas s on s.schema_id=o.schema_id 
where si.type>0 and o.type='U'
order by s.name, o.name, si.index_id