Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 16 '13 at 22:11

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

up vote 5 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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