9

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?

2
  • Do you think there would be a difference if the primary key was not the clustered column of the table? :) – LowlyDBA - John M Jun 8 '15 at 21:56
  • 1
    @JohnM: I would guess so, but I don't know. (That is why I am asking :) – Vaccano Jun 8 '15 at 21:59
14

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.

4
  • What if, defying all rationality, you made your primary key a non-clustered index and created a different clustered index? Would compression then be the same again? – Ross Presser Jun 11 '15 at 13:51
  • 1
    It would not. Don't think about primary key here, it's irrelevant. We need to focus on indexes here, clustered and non-clustered. You can't separately compress a clustered index and a table because they're the same object. clustered indexes/tables and non-clustered indexes are different objects and have to be compressed individually. – Mike Fal Jun 11 '15 at 14:12
  • I expressed myself badly. Your answer is exactly what I was driving at -- the clustered index can be compressed (whatever it is indexing) or the heap can be compressed (if there is no clustered index), not both. And a non-clustered index can also be compressed, separately. – Ross Presser Jun 11 '15 at 14:36
  • Exactly, but to be clear, you can't have a heap and a clustered index on the same table. It's one or the other. Because of how SQL Server behaves, it's common that people confuse primary keys with clustered indexes, so I want to make sure the difference is understood for this answer. – Mike Fal Jun 11 '15 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.