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);


  1. Great script is there away to use this for Table compression not index

    1. Table compression is just compression of the Clustered index. So the above scripts should provide the information you are seeking.

  2. Great script! This combined with my quicky script (below) to find DB's with Enterprise features provided me with exactly what I needed to easily find and remove compression so we could downgrade from Enterprise to Standard. Thank you!

    EXEC sp_msforeachdb 'use [?]
    DECLARE @dbname varchar(25)
    SET @dbname= ''?''

    DECLARE @tsql nvarchar(1000)
    SET @tsql = ''SELECT DB_NAME() dbName, feature_name, feature_id FROM sys.dm_db_persisted_sku_features''
    EXEC (@tsql)'