Sql-server – Should I add page level compression before adding the primary key, or after

sql serversql-server-2008

Situation

  • Data warehouse on Sql Server 2008 Enterprise
  • 36+ million row heap (don't ask), with 60+ columns
  • 750k added monthly
  • No primary key defined (I have now identified one)
  • No compression

What I'm thinking of doing (in this order)

  • Add page level compression
  • Add the PK
  • Add a number of non-clustered indexes
  • Do this as quickly as possible

Question

What I'm leaning towards at the moment

-- Add page level compression
alter table     dbo.TableName
rebuild with    (data_compression = page)
; 
go

-- Add primary key
alter table             dbo.TableName
add constraint          PK_TableName
primary key clustered   (<Columns>)
;
go

-- Add NC_IXs here
...
...

Ive looked here (PK creation documentation) and here (ALTER TABLE documentation), but can't see anything definitive about whether or not any indexes inherit table compression settings. The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

Best Answer

The clustered index is in fact the table. On the assumption that your primary key is clustered then I would create a clustered primary key with page level compression rather than trying to do it in two steps.

-- Add primary key
ALTER TABLE             dbo.TableName
ADD CONSTRAINT          PK_TableName
PRIMARY KEY CLUSTERED   (<Columns>)
WITH (DATA_COMPRESSION = PAGE)
;

I would also copy about 100k rows to a temporary (temporary physical not #temporary) table and run some tests. Try running compression first, clustered key first, try doing them as one step. See what runs fastest. I would guess it will be one step personally :).

Related Question