Tuesday, March 20, 2012

TSQL to identify Compression Type on Indexes within a SQL 2008 or greater database

I came across a need to identify which indexes within a SQL 2008 R2 database that have Compression enabled and more importantly those that do not. I could not find a specific query to do this online but have come up with the following.

This query will not only report the Indexes and their compression type for User Tables it will also generate the statements to enable PAGE or ROW compression, or remove the compression from the table.

SELECT sys.schemas.name AS [SchemaName]
, sys.objects.name AS [TableName]
, ISNULL(sys.indexes.name, '') as [IndexName]
, SUM(sys.partitions.Rows) AS [RowCount]
, sys.partitions.data_compression_desc AS [CompressionType]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' as [PAGECompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW)' as [ROWCompressionTSQL]
, 'ALTER INDEX ['+ISNULL(sys.indexes.name, '')+'] ON ['+sys.schemas.name+'].['+sys.objects.name+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE)' as [REMOVECompressionTSQL]
FROM sys.partitions
INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
AND sys.objects.type = 'U' --USER_TABLE
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id
LEFT JOIN sys.indexes ON sys.partitions.object_id = sys.indexes.object_id
AND sys.partitions.index_id = sys.indexes.index_id
WHERE sys.partitions.index_id >= 0 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(sys.partitions.object_id) LIKE '%' --Enter the table name here to restrict the results
GROUP BY sys.schemas.name
, sys.objects.name
, ISNULL(sys.indexes.name, '')
, data_compression_desc
ORDER BY data_compression_desc
, SUM(sys.partitions.Rows) DESC OPTION (RECOMPILE);