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)

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.


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.

  • 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.

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.