How do I determine whether a SQL Key column was created with compression or not?

Question

Consider the following DDL:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT [PK_MyKey] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, DATA_COMPRESSION=PAGE)
GO

It's a compressed Primary Key. Tada!

What's bothering me is that I don't see any record of that compression in SSMS?

• I don't find it in the Table, Column or Key Properties dialogs, from the context menus of the Object Explorer.
• If I ask SSMS to script => CREATE the key, then it doesn't include that DATA_COMPRESSION setting. I get:

/****** Object:  Index [PK_MyKey]    Script Date: 07/09/2020 11:01:16 ******/
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [PK_MyKey] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

So ... is it possible to tell whether or not an existing key (which I didn't create, and who's creation isn't Version Controlled :sigh:) was compressed?

Answer 1

Per the documentation this can be found in the sys.indexes and sys.partitions objects:

Metadata

The following system views contain information about data compression for clustered indexes:

• sys.indexes (Transact-SQL) - The type and type_desc columns include CLUSTERED COLUMNSTORE and NONCLUSTERED COLUMNSTORE.
• sys.partitions (Transact-SQL) - The data_compression and data_compression_desc columns include COLUMNSTORE and COLUMNSTORE_ARCHIVE.

The procedure sp_estimate_data_compression_savings (Transact-SQL) can also apply to columnstore indexes.

For the above, specifically, you want to have a look at the column data_compression and/or data_compression_desc:

CREATE TABLE dbo.MyTable_Comp (ID int NOT NULL);
ALTER TABLE dbo.MyTable_Comp
ADD CONSTRAINT [PK_MyKey_C] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF, DATA_COMPRESSION=PAGE)
GO

CREATE TABLE dbo.MyTable_NoComp (ID int NOT NULL);
ALTER TABLE dbo.MyTable_NoComp
ADD CONSTRAINT [PK_MyKey_NC] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF);

GO

SELECT i.[name], p.[data_compression], p.data_compression_desc
FROM sys.indexes i
     JOIN sys.partitions p ON i.object_id = p.object_id
WHERE i.[name] IN ('PK_MyKey_C','PK_MyKey_NC');

GO
DROP TABLE dbo.MyTable_Comp;
DROP TABLE dbo.MyTable_NoComp

For the above 2 tables, this returns the following:

name        | data_compression | data_compression_desc
------------|------------------|----------------------
PK_MyKey_C  | 2                | PAGE
PK_MyKey_NC | 0                | NONE