HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does adding page compression to a table also compress existing indexes?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
compressioncompressaddingindexesalsopagedoesexistingtable

Problem

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.

Solution

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.

Context

StackExchange Database Administrators Q#28032, answer score: 8

Revisions (0)

No revisions yet.