SQL Server › Partitioning

What requirements must be satisfied for the ALTER TABLE SWITCH PARTITION operation to work correctly when moving data between tables?

Advanced

Answer

Both the source and target tables must have identical column definitions, matching indexes, and matching constraints, and the data being switched must fully satisfy the partition boundary of the target partition, since SQL Server performs this operation as a fast metadata change rather than physically moving any data, requiring everything to already line up correctly beforehand.

-- Both tables must have matching schema, indexes, and constraints
CREATE TABLE SalesArchive (
  SaleId INT, SaleDate DATE, Amount DECIMAL(10,2)
) ON SalesPartitionScheme(SaleDate);

Real-world example A team encounters an error when trying to switch a sales partition into an archive table, discovering a missing index on the archive table that needed to exactly match the source table before the fast switch operation would succeed.

Common follow-ups: What specific error occurs if the schemas do not match exactly?;Does the target table for a partition switch need to already be partitioned itself?

Constraints (Primary Key Foreign Key Check & Unique);Indexes