Difference Between Compression on Primary Key vs Table in SQL Server 2012

compressionsql serversql-server-2012

Data compression can be set on the table:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED 
 (
    SomeId Desc
 )
) ON SomePartitionScheme(SomeId) WITH (DATA_COMPRESSION=PAGE)

And it can be defined on the Primary key:

CREATE TABLE dbo.SomeTable(
    SomeId [bigint] NOT NULL,
    OtherId [bigint] NOT NULL,
    IsActive [bit] NOT NULL,
 CONSTRAINT [PK_Some] PRIMARY KEY CLUSTERED 
 (
    SomeId Desc
 ) WITH (DATA_COMPRESSION=PAGE)
) ON SomePartitionScheme(SomeId)

But if you put it on both then you get this error:

The DATA_COMPRESSION option was specified more than once for the table, or for at least one of its partitions if the table is partitioned.

Is there any difference to putting it on the PK vs on the table?

Best Answer

It's not a matter of putting compression on a primary key, but instead a matter of putting the compression on the clustered index. For SQL Server, a clustered index is organizing the table's physical structure on that index. Or, in shorter form, the clustered index is the table. This means that compressing the clustered index and compressing the table are functionally equivalent. If you were to create your primary key as a non-clustered index and keep the base table as a heap, these two structures would be different and compressed separately.

Related Question