How much space would compressing a particular index will save? How will this affect query performance? Derek Colley walks you through the effects of using data compression in SQL Server.
In my last tip, I shared some ideas for determining if you should consider breaking up the e-mail addresses you're storing, even putting the domain names in a separate table. I performed storage and timing comparisons for working with 10,000 unique e-mail addresses, but I completely ignored data compression. I wanted to revisit the same test case and apply data compression to the tables and see how that impacted the outcome.
Making it easier to handle huge data sets
Data compression is a key feature with SQL 2008. This script, can compress all DB objects (partition/non-partitioned) according to Partition Range you specifiy.