Compress all Tables and Indexes

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Not Supported

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 20 Feb 2010

Description

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.

Code

Compress Tables:

--Creates the ALTER TABLE Statements

SET NOCOUNT ON

SELECT

'ALTER TABLE ['

+ s.[name]

+ '].['

+ o.[name]

+ '] REBUILD WITH (DATA_COMPRESSION=PAGE);'

FROM sys.objects AS o WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON o.[object_id] = i.[object_id]

INNER JOIN sys.schemas AS s WITH (NOLOCK)

ON o.[schema_id] = s.[schema_id]

INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)

ON i.[object_id] = ps.[object_id]

AND ps.[index_id] = i.[index_id]

WHERE o.[type] = 'U'

--AND s.Name LIKE '%%' -- filter by table name

--AND o.Name LIKE '%%' -- filter by schema name

ORDER BY ps.[reserved_page_count]

SET NOCOUNT OFF

Compress Indexes:

--Creates the ALTER INDEX Statements

SET NOCOUNT ON

SELECT

'ALTER INDEX ['

+ i.[name]

+ '] ON ['