Recently we’ve encountered an interesting use case for a customer.
They had a table with partitioning that they needed to be archived (with the help of said partitioning), but the ALTER TABLE SWITCH commands were failing with a peculiar error:
Error 4907, ‘ALTER TABLE SWITCH’ statement failed. The table ‘MyDB.dbo.PrtTable1’ has 4 partitions while index ‘IX1’ has 6 partitions.
This prompted some insightful research into some of the validations done by SQL Server behind the scenes when executing an ALTER TABLE SWITCH command.
When it comes to managing tables and indexes in SQL Server, the ALTER TABLE SWITCH statement is a powerful tool for “moving” data swiftly between tables. However, this convenience can sometimes be met with frustrating roadblocks, such as errors 4907 and 4908.
These errors may be confusing about their underlying cause, particularly when the source and target tables have identical partitions, including in non-clustered indexes.
To learn more, read my full post at the official Madeira Data Solutions blog:
Understanding ‘ALTER TABLE SWITCH statement failed’ errors 4907, 4908, and 4912