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.

I am creating a series of new tables that will have page level compression enabled.

Will this affect the indexes as well, or will I have to also specify compression when creating the indexes?

create table Message
(
  pk_Message   int identity  NOT NULL constraint PK_Message primary key,
  [Message]    varchar(900)  NOT NULL constraint UK_Message__Message unique,
  DateCreated  datetime      NOT NULL default getdate()
) with (data_compression = page)
go  

AS you can see in my example, I plan on creating a unique constraint on a rather large data type, and want to take advantage of compression.

share|improve this question

2 Answers 2

up vote 8 down vote accepted

From: http://msdn.microsoft.com/en-us/library/cc280449%28v=sql.105%29.aspx

The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually.

share|improve this answer
3  
Correcto. I also blogged about this - there's no inheritance for compression settings: "When you compress tables and indexes, it’s a one-time action. You’re only taking care of what exists today. If someone (or even you) turns around and creates an index on that same table tomorrow, it won’t be compressed by default. Whoever creates the index has to make sure that it’s compressed, and there’s nothing in SQL Server Management Studio that will hint to them that other parts of that same object are compressed." brentozar.com/archive/2009/08/… –  Brent Ozar Nov 1 '12 at 22:06

Negative, if you open up your indexes, right click, go to storage, and compression. Notice that it shows "none" as the compression type.

Also, each partition needs to be compressed. By default the data and index files have only 1 partition though.

share|improve this answer

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.