It’s been a while since I worked with compression and the other day I needed to check which of my indexes were compressed and which weren’t. Now, I knew the information wasn’t going to be in sys.tables and I couldn’t find it in sys.indexes or INDEXPROPERTY(). I’ll be honest it had me stumped for a little bit. Until I remembered something!
Compression isn’t done at the table or even the index level. It’s done at the partition level. Something important to remember is that every table has at least one entry in sys.indexes, although in the case of a heap it’s just the unindexed table. So in a way you could say that every table has an index. Well every index has at least one partition. If you haven’t deliberately partitioned the index it’s just the whole index.
Why would you want to compress just certain partitions? Well, remember that one reason for partitioning is to separate out older, less used, data from newer, more frequently accessed data. You might decide to use page compression, the strongest but slowest compression, on your oldest data to conserve space on data you aren’t going to have to de-compress very often. You might then have a group of data that you access a bit more often where you use row compression, which saves some space and takes a bit less CPU to undo. And on your current/more frequently accessed data you don’t compress the data at all.
Anyway, back on point. The system view sys.partitions has the information we are looking for. This query has the table and index names along with the partition information.
SELECT o.name, i.name, p.* FROM sys.partitions p JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.objects o ON i.object_id = o.object_id
