Monday, February 18, 2013

Querying Compressed Objects

If you use page or row compression, which is a feature new to SQL Server 2008, I've found it rather cumbersome to quickly identify which objects are being compressed.  Below is a quick query I put together to help me locate these objects - hopefully it helps you also!

SELECT  schema_ = SCHEMA_NAME(sys.objects.schema_id)
       ,table_ = OBJECT_NAME(i.object_id)
       ,index_ = i.name
       ,rows
       ,data_compression_desc
       ,i.type_desc
  ,i.index_id
FROM    sys.partitions
        INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id
        INNER JOIN sys.indexes AS i ON sys.objects.object_id = i.object_id AND sys.partitions.INDEX_id = i.index_id
WHERE   data_compression > 0
        AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY schema_
       ,table_