Compress all Tables and Indexes
SQL Server 2000: Not Supported
SQL Server 2005: Not Supported
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 20 Feb 2010
Data compression (at the row and page level) was a feature introduced in sQL Server 2008.
There are limitations as to which objects that compression can be implied and it is best to look these up in SQL BOL.
Compression can be applied to an individual table, but the non-clustered indexes for the table must be compressed separately. Compression can be achieved via TSQL, but this can get a bit verbose where there are multiple indexes per table.
The scripts below will generate TSQL compression activation code for all or some of the tables and indexes in a database.
They are provided in a form that will include all tables or indexes, but they can be easily customised by uncommenting and changing the provided 'AND' clauses.
--Creates the ALTER TABLE Statements