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?
al44ncAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Indexes on both the tables should have to the same settings, otherwise its going to throw an index mismatch error
0
Scott PletcherSenior DBACommented:
Yes, all indexes must exist, and the core definitions must match -- same key columns, uniqueness specification, etc..  

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.
0
al44ncAuthor Commented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Anthony PerkinsCommented:
Well, you did not save me time, guys, and that was the idea.
So sorry, perhaps next time.
0
al44ncAuthor Commented:
I'll take it. Here is an advance :-)
0
Anthony PerkinsCommented:
I have asked an EE Moderator to delete this question.
0
Anthony PerkinsCommented:
By the way a cursory search in Google (see for yourself click here) reveals the following from MS Technet:
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.
0
al44ncAuthor Commented:
Partitioned Table and Index Strategies Using SQL Server 2008
Switching Data Out
To switch data out of a partitioned table:
1.      Create an empty staging table on the same filegroup as the partition to be switched out with the same structure (including the partition column).
2.      Create identical clustered and (optionally) nonclustered indexes on the empty staging table.
3.      Optionally, create a check constraint on the stand-alone table to ensure that all values of the partition column will be in the same range as the partition that will be switched out.
--------------------
So the answer is: the nonclustered indexes on the empty table are not necessary, but if created their definition should be identical to the ones on the partition-to-switch-out.
--------------------
Well, it was a good question. May be short-lived, but definitely not easy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
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.
0
al44ncAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.