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.
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.
14 People are following this question.
