question

Magnus Ahlkvist avatar image
Magnus Ahlkvist asked

Page compression and partition switching

Hi! I have a partitioned table with an accompanying staging table which I switch into the table. I am now implementing page compression on both the table and the non clustered indexes. The loading is pretty standard: - Disable nonclustered indexes on the staging table - Load data - Rebuild indexes, with page compression - Add check constraint to fit with the boundaries in the empty partition of the partitioned table - Switch staging table to the empty partition - Drop check constraint on the staging table The partitioned table and the staging table have the same indexes, so that I can use fast partition switching. Must the empty partition in the partitioned table also have page compression enabled, or will fast partition switching work anyway? Not that it makes any difference, since enabling page compression on an empty partition will be a pretty quick operation, but it adds extra code to maintain in the loading process.
sql-server-2008partitioncompression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I was a little too quick to post this question. Documentation is clear The compression of the staging table must match the compression of the partition it is to be switched into. Plus I really have nothing to worry about in terms of maintaining extra code for the loading: The SPLIT-operation takes care of setting the correct data compression on the new partition.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.