patternsqlMinor
Does adding page compression to a table also compress existing indexes?
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?
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.
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)
goAS 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.
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.