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 ['
+ 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 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 i.[index_id] >0
--AND i.Name LIKE '%%' -- filter by index name
--AND o.Name LIKE '%%' -- filter by table name
ORDER BY ps.[reserved_page_count]
SET NOCOUNT OFF
|
