Using SQL Server 2012. I have a huge table that is partitioned. I need to create a non clustered index on it.

After I created the index, a job that somewhere switches partition fails because the index I create. (The job works fine once I remove the index)

My question is what are the things I need to take in account while creating the index on partitioned table.


Your job most likely blew up because you added the non-clustered index to the base table, but did not add it to the staging table required for switching partitions in and out.

There are a significant number of things to consider when using partition switching - please read Transferring Data Efficiently by Using Partition Switching. From that post.

Nonclustered indexes must be defined and must be identical. Any nonclustered indexes that are defined on the target table are also defined on the source table and are structured identically in terms of uniqueness, subkeys, and the sorting direction (ASC or DESC) for each index key column. Nonclustered indexes that are disabled are exempt from this requirement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.