Understanding 'ALTER TABLE SWITCH statement failed' errors 4907, 4908, and 4912

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.

Problem

In our particular use case, when trying to perform SWITCH TO between two tables, errors 4907 or 4908 are raised. For example:

Msg 4907, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'IX1' has 6 partitions.

Msg 4908, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The range boundary values used to partition the table 'MyDB.dbo.PrtTable1' are different from the range boundary values used for index 'IX1_3'.
 

This normally happens when the source table has a non-clustered index that's partitioned differently from the table itself (i.e. the heap or the clustered index).

But this error is also raised when:

• Both source and target tables are partitioned identically, including their nonclustered indexes.

• The target table doesn't have any non-clustered indexes (in which case, the validation on non-clustered indexes should be irrelevant).

To simulate this issue, let's first create our partition functions and corresponding partition schemes:

SET NOCOUNT ON;
GO
CREATE PARTITION FUNCTION PF1 (int) AS
 RANGE RIGHT FOR VALUES (0, 100);
 
CREATE PARTITION SCHEME PS1 AS PARTITION PF1 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION PF2 (int) AS
 RANGE RIGHT FOR VALUES (0, 100, 200);
 
CREATE PARTITION SCHEME PS2 AS PARTITION PF2 ALL TO ([PRIMARY]);
GO
CREATE PARTITION FUNCTION PF3 (int) AS
 RANGE RIGHT FOR VALUES (0, 200);
 
CREATE PARTITION SCHEME PS3 AS PARTITION PF3 ALL TO ([PRIMARY]);
GO

Our partition functions translate to the following 3 sets of ranges:

• PF1: 0, 100

• PF2: 0, 100, 200

• PF3: 0, 200

Next, we'll create our source table with a clustered index using the first partition function (mapped to partition scheme PS1):

-- PrtTable1: The source table
CREATE TABLE dbo.PrtTable1
(
	prtCol int NOT NULL
	 CONSTRAINT PK_PrtTable1 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
) ON PS1(prtCol)

and we'll also create the following nonclustered indexes on it using the other two partition functions (mapped to partition schemes PS2 and PS3 respectively):

CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1
 (otherCol) ON PS2(prtCol)
CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1
 (otherCol) ON PS3(prtCol)

Next, we'll create our target table with the exact same structure and usage of partition functions and schemes as our source table:

-- PrtTable2: Target table partitioned and indexed identically to source table PrtTable1
CREATE TABLE dbo.PrtTable2
(
	prtCol int NOT NULL
	 CONSTRAINT PK_PrtTable2 PRIMARY KEY CLUSTERED ON PS1(prtCol),
	otherCol int NOT NULL
) ON PS1(prtCol)

CREATE NONCLUSTERED INDEX IX2 ON dbo.PrtTable2
 (otherCol) ON PS2(prtCol)
CREATE NONCLUSTERED INDEX IX2_3 ON dbo.PrtTable2
 (otherCol) ON PS3(prtCol)

And we'll create one more target table, this time on PF3 and without any nonclustered indexes:

-- PrtTable3: Target table without any nonclustered indexes, partition range not identical but contains the range from source table
CREATE TABLE dbo.PrtTable3
(
	prtCol int NOT NULL CONSTRAINT PK_PrtTable3 PRIMARY KEY CLUSTERED ON PS3(prtCol),
	otherCol int NOT NULL
) ON PS3(prtCol)

Now, to begin our experiment, we'll insert just one row into the source table, which will go into partition number 2:

-- Insert just one row into source table, goes into partition number 2
INSERT INTO dbo.PrtTable1 (prtCol, otherCol) VALUES(50, 30)

Now we want to switch our data from partition 2 to the target table with the exact structure:

ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2

Source: PrtTable1	Target: PrtTable2
PK (clustered) on PS1	PK (clustered) ON PS1
IX1 ON PS2	IX2 ON PS2
IX1_3 ON PS3	IX2_3 ON PS3

Unfortunately, that will raise error 4907.

Msg 4907, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'IX1' has 6 partitions.

The same thing will happen if we try to switch from partition 2 into the target table that doesn't have any nonclustered indexes:

ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2

Source: PrtTable1	Target: PrtTable3
PK (clustered) on PS1	PK (clustered) ON PS3
​IX1 ON PS2
IX1_3 ON PS3

Result:

Msg 4907, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'IX1' has 6 partitions.

Okay, so SQL Server doesn't like the mismatched number of partitions at the source table. What if we recreate one of the indexes so that all indexes have the same number of partition ranges?

CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
GO
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2

Source: PrtTable1	Target: PrtTable2
PK (clustered) on PS1	PK (clustered) ON PS1
​IX1 ON PS1	IX2 ON PS2
IX1_3 ON PS3	IX2_3 ON PS3

The switch still fails, this time with error 4908:

Msg 4908, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The range boundary values used to partition the table 'MyDB.dbo.PrtTable1' are different from the range boundary values used for index 'IX1_3'.

That's because we still have the other nonclustered index that has different range boundaries.

Let's try recreating that one as well so that now the entire source table is on the exact same partition scheme and function:

CREATE NONCLUSTERED INDEX IX1_3 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON) ON PS1(prtCol)
GO
ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2

Source: PrtTable1	Target: PrtTable2
PK (clustered) ON PS1	PK (clustered) ON PS1
IX2 ON PS1	IX2 ON PS2
IX2_3 ON PS1	IX2_3 ON PS3

But this still fails with error 4907, this time on the target table:

Msg 4907, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable2' has 4 partitions while index 'IX1' has 6 partitions.

Yes, that's because the target table also has nonclustered indexes that are not perfectly aligned with the base table.

But what about the second target table? The one without any nonclustered indexes?

ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2

Source: PrtTable1	Target: PrtTable3
PK (clustered) ON PS1	PK (clustered) ON PS3
IX2 ON PS1
IX2_3 ON PS1

This time the switch works! Even though the range boundaries are different (0,100 in PS1 vs 0,200 in PS3). But the ranges are still matching (i.e. 0,100 is contained within 0,200), so the switch is successful.

The full script is also available in my GitHub Gists:

https://gist.github.com/EitanBlumin/1ad9366b3c864cd9f60eff766c2cacf8

These validations, evident by errors 4907 and 4908, don't make much logical sense at first. When the source and target tables are identically partitioned then it shouldn't matter that a non-clustered index is differently partitioned from the table itself... Right?

Even more so if the same non-clustered index doesn't even exist at all on the target table (see PrtTable3 in the code snippet above). And even more so when the partition range values are matching between the source and target across the board.

Solution

Unfortunately, as of SQL Server 2022, there is no direct solution to this. This is the intended behavior of the SQL Server database engine.

However, there's currently no mention or explanation of errors 4907 and 4908 or the rationale behind them in Microsoft's documentation anywhere. 😕

(actually, there's no definitive list of ALTER TABLE SWITCH restrictions anywhere, but that's a problem for another time. Meanwhile, I already opened a GitHub issue about the missing documentation)

But... Going beyond my initial intuition about this, I realized that there's a good reason for these limitations. 🤔

ALTER TABLE SWITCH statement failed - for good reason

When there's a non-clustered index on the table that is partitioned differently from the table itself, then the reference to any partition number in the SWITCH command becomes ambiguous (huh? partition number based on what? the clustered index? nonclustered index 1? nonclustered index 2? what's this? who are you? who am I?? 😵).

I can understand the rationale in this case, even though I believe a solution in T-SQL should be implemented to remove this ambiguity at least for some cases. Such as an optional reference to a specific partition scheme/function in the SWITCH command. For example:

ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable2 PARTITION 2 WITH (RANGE BY PARTITION FUNCTION PF1) -- I'm just spitballing here

Alternatively, something could be added to specify a partition number per index (assuming that the ranges match between the source and target and on all non-clustered indexes).

For example:

ALTER TABLE dbo.PrtTable1 SWITCH (PARTITION 2 ON INDEX PK_PrtTable1, PARTITION 2 ON INDEX IX2) TO dbo.PrtTable2 (PARTITION 2 ON INDEX PK_PrtTable2, PARTITION 2 ON INDEX IX2)

I can still foresee a potential problem with data integrity in some cases, though.

For example, if the range boundaries don't quite match, and/or the partition key column set is different, then the database engine will not be able to deterministically know which data to remove (switch out) from all indexes on the source table. Let's see it illustrated:

Illustration comparing differently partitioned non-clustered indexes vs the clustered index

Let's split this into several use cases, assuming that we want to switch out PARTITION 2 from a source table:

• clustered primary key PK (i.e. the table itself)

  • The partition key column is col1

  • Partition scheme PS1 is partitioned using ranges (0, 100). This creates 3 ranges:

    • 1: < 0

    • 2: 0 <= X < 100

    • 3: 100 <=

  • The number of partition ranges is 3

• non-clustered index IX1

  • Same partition key column (col1)

  • Partition scheme PS2 is partitioned using ranges (0, 100, 200). This creates 4 ranges:

    • 1: < 0

    • 2: 0 <= X < 100

    • 3: 100 <= X < 200

    • 4: 200 <=

  • Partition 2 has the same range values as partition 2 on the clustered index

  • The existence of this index throws error 4907

• non-clustered index IX1_3

  • Same partition key column (col1)

  • Partition scheme PS3 is partitioned using ranges (0, 200). This creates 3 ranges:

    • 1: < 0

    • 2: 0 <= X < 200

    • 3: 200 <=

  • Same number of partition ranges (3)

  • Partition 2 does NOT have the same range values as partition 2 on the clustered index

  • The existence of this index throws error 4908

• non-clustered index IX1_2

  • Different partition key column (col2)

  • Same partition scheme as the clustered index (PS1)

  • Same number of partition ranges (3)

  • Partition 2 has the same range values as partition 2 on the clustered index

  • The existence of this index would raise error 4912

As you can see, I just added another index IX1_2 to illustrate error 4912 which has a similar rationale. Example error message:

Msg 4912, Level 16, State 1, Line ##
'ALTER TABLE SWITCH' statement failed. The columns set used to partition the table 'MyDB.dbo.PrtTable1' is different from the column set used to partition index 'IX1_2'.

This validation, evident by error 4912, is done because the non-clustered index is partitioned based on a column different from the partition key of the clustered index. Therefore, SQL Server cannot deterministically match the data across the indexes.

In other words, partition 2 in the clustered index does not necessarily equal partition 2 in the non-clustered index IX1_2. Even if they have the exact same range boundaries... Because the partition column is different.

It's kinda like the fact that these two statements are not equal to each other:

SELECT * FROM Table1 WHERE col1 BETWEEN 0 AND 100
SELECT * FROM Table1 WHERE col2 BETWEEN 0 AND 100

(different column in the WHERE clause)

I realized that there's no way to solve errors 4908 and 4912 with improvements to the TSQL syntax, even if there's a total match between the source and target tables, and even if there are no non-clustered indexes on the target table.

This is because there would be no way to deterministically match the data in one range to its counterparts in another range, and therefore, the partition cannot be safely removed.

But wait, how were we able to successfully run this command?

ALTER TABLE dbo.PrtTable1 SWITCH PARTITION 2 TO dbo.PrtTable3 PARTITION 2

After all, while the source table is partitioned by ranges (0,100) in PS1, the target table here is partitioned by ranges (0,200) in PS3... So, why does it still work but not when the same misalignment is in a nonclustered index?

Well, yes, that's correct. The problem when this happens at the source table (i.e. mismatch between the heap/clustered and a non-clustered index) is because SQL Server wouldn't know which "subset" of data it needs to remove from the non-clustered index.

In order to validate something like this, it would have to perform a data-size operation by scanning the data.

In the case of the target table, though, all it needs to do is check that the destination partition is empty and that's it. There's no data-size operation to be done here because there's no data to be checked anyway.

With that being said, I think error 4907 should be solvable.

But in order for this to work, the database engine would need to validate that the ranges match precisely across all indexes on the source and target tables.

This means that the following conditions must be evaluated:

• The boundary range values of the source and target partitions must be identical across all indexes in both the source and target (the target partitions should be logically matching but don't have to be identical, as they'll have to be empty anyway).

• The partition key column set (and data type) must be identical across all indexes in both the source and target.

Assuming that the above conditions are evaluated to be true, it should not matter how many other partitions there are across different indexes at the source table.

TO CLARIFY: This is NOT how this actually works in SQL Server as of the time of this writing. It's just an idea. Vote for this product feedback item if you agree that it should be implemented in TSQL.

Conclusion

As I said, there's currently no available solution to avoid errors 4907, 4908, and 4912. If your non-clustered indexes are not identically partitioned in terms of the number of partitions, boundary range values, and partition key column set, then you simply can't use the SWITCH command for that table.

Not until you re-align your indexes by re-creating them on the correct partition scheme and partition key, as we did in our sample script in the beginning. For example:

CREATE NONCLUSTERED INDEX IX1 ON dbo.PrtTable1 (otherCol) WITH(DROP_EXISTING = ON, ONLINE = ON) ON PS1(prtCol)

Obviously, this is a data-size operation that may take a very long time to run depending on your table size, and may not always be possible to do online. But given the current database engine limitations, there aren't many other options available.

But Wait, There's More!

And this doesn't end here either.

Check it out: Errors 4907, 4908, and 4912 are stored in sys.messages as such:

4907:
'%ls' statement failed. The %S_MSG '%.*ls' has %d partitions while index '%.*ls' has %d partitions.

4908:
'%ls' statement failed. The range boundary values used to partition the %S_MSG '%.*ls' are different from the range boundary values used for index '%.*ls'.

4912:
'%ls' statement failed. The columns set used to partition the %S_MSG '%.*ls' is different from the column set used to partition index '%.*ls'.

Notice the '%ls' placeholder where "ALTER TABLE SWITCH" should be? This indicates that this issue could potentially happen with other commands, not just ALTER TABLE SWITCH.

I can't tell you which commands those would be, though. Maybe TRUNCATE TABLE WITH PARTITIONS?

Furthermore, there are currently 61 error messages between error IDs 4900 and 4993 that have the words "partition" or "SWITCH" in them. All of which reflect restrictions of the ALTER TABLE SWITCH command in one way or another.

I hope that you agree with me that the Microsoft documentation should be updated to reflect these limitations wherever relevant.

Check out the GitHub Issue that I opened for this.

And also, vote for this product feedback item if you agree with me that a proper solution should be implemented at least for error 4907 (the one about having a different number of partitions, even though the ranges match).

This post is dedicated to Shalev Bartal, thanks to whom we've encountered this very peculiar use case :)