Should I add page level compression before adding the primary key, or after?

Question

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

• Ultimately: Do I add the PK or the page compression first? (Does it matter?)
• If I add the compression first to the table, will any indexes then inherit the table level compression settings? The answer to this particular question is "No, compression is not inherited", found here on dba.stackexchange

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

Answer 1

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 :).

Answer 2

Either way, page compression is going to shuffle data around a LOT. I would estimate doing the compression first would result in less overall I/O since the clustering operation will be reading compressed pages.