I took a backup of one of our test databases today and gave it to someone so that it could be restored on one of their servers.
I got back the following in an email from the person who tried to do the restore
Date 11/16/2012 12:58:16 PM
Log SQL Server (Current – 11/16/2012 1:00:00 PM)
Database ‘YourCrappyDB’ cannot be started in this edition of SQL Server because part or all of object ‘CrappyIndexData’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Okay, so they are running the standard edition of SQL Server. How can you quickly find all the tables and indexes that use compression? Let’s take a look, first we are going to create three tables, a heap, a table with a non clustered index and a table with a clustered index
A table without indexes (a heap)
CREATE TABLE TestCompress(SomeCol VARCHAR(1000)) GO ALTER TABLE TestCompress REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
A table with a non clustered index
--Non clustered index CREATE TABLE TestCompress2(SomeCol VARCHAR(100) NOT null) GO CREATE NONCLUSTERED INDEX IX_TestCompress2 ON TestCompress2 (SomeCol) WITH ( DATA_COMPRESSION = ROW ) ; GO
A table with a clustered index
--Clustered index CREATE TABLE TestCompress3(SomeCol VARCHAR(100) NOT null) GO CREATE CLUSTERED INDEX IX_TestCompress3 ON TestCompress3 (SomeCol) WITH ( DATA_COMPRESSION = ROW ) ; GO
Here is the query that will give you the table name, the storage type, the index name if there is one and the type of compression
SELECT DISTINCT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) AS TableName, i.name AS IndexName, p.data_compression_desc AS CompressionType, i.type_desc AS StorageType FROM sys.partitions p INNER JOIN sys.objects o ON p.object_id = o.object_id JOIN sys.indexes i ON p.object_id = i.object_id AND i.index_id = p.index_id WHERE p.data_compression > 0 AND SCHEMA_NAME(o.schema_id) <> 'SYS'
Here is the output of that query
Now why do I have a distinct in my query? The reason is that if you have your indexes/tables partitioned you will get more than one row per index. you can add p.rows to the select portion of the queries and you will see how many rows each partition holds
Hopefully this will help someone in the future