al44nc
asked on
Compression settings when switching SQL partitions
When switching partitions, does the empty table need to match data comression settings on all indexes or only on the clustered index?
I.e. if the clustered index has DATA_COMPRESSION = 'NONE' and there is a nonclustered index with DATA_COMPRESSION = 'PAGE' does the empty table need to copy the nonclusted index settings?
Does the empty table need to have all the same nonclustered indexes at all?
I.e. if the clustered index has DATA_COMPRESSION = 'NONE' and there is a nonclustered index with DATA_COMPRESSION = 'PAGE' does the empty table need to copy the nonclusted index settings?
Does the empty table need to have all the same nonclustered indexes at all?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, you did not save me time, guys, and that was the idea.
I did some testing and found that the empty table does not need nonclustered indexes at all, even if partitioned table has it.
I will still accept a comment as a solution to this modified question:
If the empty table has all the same nonclustered indexes their compression settings should match the corresponding partitioned table n-c indexes settings.
I need a Yes/No answer with an authoritative link or test code.
I did some testing and found that the empty table does not need nonclustered indexes at all, even if partitioned table has it.
I will still accept a comment as a solution to this modified question:
If the empty table has all the same nonclustered indexes their compression settings should match the corresponding partitioned table n-c indexes settings.
I need a Yes/No answer with an authoritative link or test code.
Well, you did not save me time, guys, and that was the idea.
So sorry, perhaps next time.
So sorry, perhaps next time.
ASKER
I'll take it. Here is an advance :-)
I have asked an EE Moderator to delete this question.
By the way a cursory search in Google (see for yourself click here) reveals the following from MS Technet:
Data Compression
Data Compression
For partitioned tables and indexes, the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am sorry, I misunderstood. From your original question, I thought you were asking about compression and specifically if all the partitions had to use the same compression setting.
ASKER
Only my comment has a comprehensive answer to the question: it is the only one that correctly answers the part regarding necessity of non-clustered indexes on the empty staging table.
I don't think the compression has to match, but, like you, I'm not 100% sure about that. You probably just need to test it.