Reputation: 21
'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is partitioned while index 'IX_test' is not partitioned."
When I run switch partition
, I get this error. To resolve this error, IX_test
index should have the partitioned column included in its definition. But if we include the partitioned column in the index, will this decrease the performance?
Example: We have Table X which has index on columns A, B, and C and it is partitioned on column D. We create one more table called X1 with the same schema as X. We will use this for a partition switch operation between them (X and X1).
As the index is not on column D (it's on A, B, C), I am getting the error above. Is it good to add the partitioned column (i.e., D) to the index as shown below?
CREATE NONCLUSTERED INDEX IX_T_MSB_Pseudo ON dbo.T_MSB_Pseudo
(
A, B, C
)
INCLUDE
(
D
)
ON PRIMARY
Will this decrease the performance? Are there other alternatives to resolve this issue?
Upvotes: 1
Views: 4630
Reputation: 432742
SWITCHing partitions requires the switched tables are pretty much* identical in every way
So make them so.
The point of partition switching it to be able to swap a few pointers to swap data in and out without any actual processing or checks or data movement. So there are rules.
From MSDN "Transferring Data Efficiently by Using Partition Switching", 2 key points
Partitions must be on the same column
Nonclustered indexes must be defined and must be identical
Upvotes: 4