question

CirqueDeSQLeil avatar image
CirqueDeSQLeil asked

SQL 2008 Compression

How do you find which tables in your database have been compressed?

sql-server-2008seeder-questionfor-fun
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If your don't have SQL Server 2008 Enterprise edition then the answer is none. Otherwise, keep digging :)
1 Like 1 ·
@Oleg - +1 for the encouraging comments!
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered

I was waiting for about an hour, and still nobody wants to answer, so I might as well go ahead. sys.partitions view has been greatly enhanced in SQL Server 2008 Enterprise edition, and it now includes data_compression and data_compression_desc columns.

Edited the "The snippet below will not run in lesser versions" statement because this is incorrect as pointed out by Fatherjack. The snippet below will not run if the version of the SQL Server is less than 2008, because sys.partitions does not have data_compression and data_compression_desc columns, will run but return 0 records if the edition is not Enterprise or Developer, and will return records but if you have compressed tables in Enterprise or Developer edition of 2008:

select 
    s.name + '.' + o.name TableName,
    p.rows RecordCount, data_compression_desc DataCompression
    from sys.indexes i inner join sys.partitions p
        on i.[object_id] = p.[object_id] and i.index_id = p.index_id
    inner join sys.objects o
        on o.[object_id] = i.[object_id]
    inner join sys.schemas s
        on o.[schema_id] = s.[schema_id] 
    where 
        p.data_compression > 0
        and i.index_id < 2
        and o.type = 'U'
        and o.name != 'dtproperties'
    order by s.name, o.name;

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.