Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION?


This is a quick answer to a question I was sent today by someone who’d read Kimberly’s partitioning whitepaper – Partitioned Tables and Indexes in SQL Server 2005 – and is implementing a “sliding-window” scenario. (This is a mechanism to allow fast insertion and deletion of significant portions of data into/from a partitioned production table. Insertion is done by taking a table and making it a new partition of the production table – called switching-in. Deletion is done by removing a partition from the production table and making it into a stand-alone table – called switching-out.)


The question is – what indexes are required on the staging table to prevent the ALTER TABLE … SWITCH PARTITION statement from failing with a message like that below:



Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘PartitionTest.dbo.StagingTable’ for the index ‘NC_Birthday’ in target table ‘PartitionTest.dbo.ProductionTable’.


The answer is that the staging table has to have the exact same indexes – clustered and non-clustered – as the production table. I asked Kimberly if it has to have the same constraints too – the answer is yes, plus the staging table has to have a trusted constraint on it such that SQL Server can tell (without checking all the data in the staging table) that all the data satisfies the partitioning function for the partition that you’re switching-in (i.e. the partition that the staging table will become in the production table). If it doesn’t, the switching-in will fail with the following error:



Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘PartitionTest.dbo.StagingTable’ allow values that are not allowed by range defined by partition 4 on target table ‘PartitionTest.dbo.ProductionTable’.


One thing that confuses people is that SQL Server does not create the target table for you when doing a switch-out of a partition. The target table has to exist and have the exact same schema as the production table. Also, it has to be completely empty – otherwise you’ll get an error like:



Msg 4905, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The target table ‘PartitionTest.dbo.StagingTable’ must be empty.


The must-be-empty requirement also holds for switching-in operations – the partition that will be created has to be empty otherwise a similar 4904 error results.


Hope this helps!

2 thoughts on “Indexes From Every Angle: What indexes are necessary when using SWITCH PARTITION?

  1. When switching out the target table has to have the clustered index, but non-clustered indexes are not required.

    Regards,

    Rob.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.