Switch Partitions

Learn more about Toad for SQL Server
at Dell Software

Find solutions and downloads at the
Toad for SQL Server Support Portal

You are here: SQL Server Objects > Tables > Manage Table Partitions > Switch Partitions

Switch Partitions

Switching a partition from one table to another eliminates the time consuming process of running a delete operation and eliminates blocking access to the partition during the process. When you switch a table partition to a non-partitioned table in the same filegroup, SQL Server accomplishes this task with a metadata change.

Because this is a special operation, it is only successful under certain conditions. The source table and the target table must meet the following conditions for each object:

Object	Source and Target Table Criteria
Boundary values	Must have the same boundary values. The source partition must fit within the boundary values of the target partition. If not, the source table must have a constraint defined to ensure that all the data fits into the target partition. The same criteria applies when you add a nonpartitioned table as a partition to a partitioned table.
Columns	Must be partitioned on the same column and have columns in the same order and with the same names, data type, length, collation, precision, scale, and nullability.
In-row	Must have the same settings for text, ntext, or image columns.
Partition Keys	Must be defined the same. When using computed columns, the expressions defining the computed columns must be the same and both must be persisted.
ROWGUID column	Must have a corresponding column in the other table with a ROWGUID property.
XML column	Must have the same XML schema.
Constraints	Must be the same for both tables.
CHECK	Must have identical structure on both tables or the source table's CHECK constraint can be a subset of the target table's CHECK constraint.
FOREIGN KEY	Must be identical in both tables and must reference the same PRIMARY keys. No active PRIMARY key or FOREIGN key relationship is allowed between the source table and the target table if one of the tables contains a FOREIGN key. A FOREIGN key in another table cannot reference the source table.
PRIMARY KEY	Must be the same for both tables.
Filegroup	Must be located in the same filegroup and be partitioned on the same column if both are partitioned.
Indexes	Must be aligned with the table whether one of the tables is partitioned or whether both are partitioned. No full-text indexes are permitted on either table and no XML indexes are permitted on the target table.
Clustered	Must have the same clustered indexes and both must be enabled.
Non-clustered	Must be structured the same and have the same uniqueness, subkeys, and ascending or descending sorting direction for each index key column. Note: Disabled nonclustered indexes are exempt from this requirement.
Permissions	Can be different for both tables.
Replication	No replication using the source or the target table as a source.
Rules	No rules can be defined on either the source or the target table .
Settings	Must have the same ANSI_NULLS and QUATED IDENTIFIER setttings.
Structure	Must have same structure (BLOBS, indexes, index partitions, etc.)
Target Table/ Partition	Target table or partition must be empty.
View	No indexed views can be on the source or the target table. You cannot use a view with schema binding.

Object

Source and Target Table Criteria

Boundary values

Must have the same boundary values. The source partition must fit within the boundary values of the target partition. If not, the source table must have a constraint defined to ensure that all the data fits into the target partition. The same criteria applies when you add a nonpartitioned table as a partition to a partitioned table.

Columns

Must be partitioned on the same column and have columns in the same order and with the same names, data type, length, collation, precision, scale, and nullability.

In-row

Must have the same settings for text, ntext, or image columns.

Partition Keys

Must be defined the same. When using computed columns, the expressions defining the computed columns must be the same and both must be persisted.

ROWGUID column

Must have a corresponding column in the other table with a ROWGUID property.

XML column

Must have the same XML schema.

Constraints

Must be the same for both tables.

CHECK

Must have identical structure on both tables or the source table's CHECK constraint can be a subset of the target table's CHECK constraint.

FOREIGN KEY

Must be identical in both tables and must reference the same PRIMARY keys. No active PRIMARY key or FOREIGN key relationship is allowed between the source table and the target table if one of the tables contains a FOREIGN key. A FOREIGN key in another table cannot reference the source table.

PRIMARY KEY

Must be the same for both tables.

Filegroup

Must be located in the same filegroup and be partitioned on the same column if both are partitioned.

Indexes

Must be aligned with the table whether one of the tables is partitioned or whether both are partitioned. No full-text indexes are permitted on either table and no XML indexes are permitted on the target table.

Clustered

Must have the same clustered indexes and both must be enabled.

Non-clustered

Must be structured the same and have the same uniqueness, subkeys, and ascending or descending sorting direction for each index key column.

Note: Disabled nonclustered indexes are exempt from this requirement.

Permissions

Can be different for both tables.

Replication

No replication using the source or the target table as a source.

Rules

No rules can be defined on either the source or the target table .

Settings

Must have the same ANSI_NULLS and QUATED IDENTIFIER setttings.

Structure

Must have same structure (BLOBS, indexes, index partitions, etc.)

Target Table/ Partition

Target table or partition must be empty.

View

No indexed views can be on the source or the target table. You cannot use a view with schema binding.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

Tip: You can right-click a table and select Storage | Partition Cleanup to remove unused partition schemes and functions.