Transferring Data Efficiently by Using Partition Switching
Partitioning data enables you to manage and access subsets of your data quickly and efficiently while maintaining the integrity of the entire data collection. You can use the Transact-SQL ALTER TABLE...SWITCH statement to quickly and efficiently transfer subsets of your data in the following ways:
Assigning a table as a partition to an already existing partitioned table.
Switching a partition from one partitioned table to another.
Reassigning a partition to form a single table.
For information about partition switching concepts and see the ReadMe_SlidingWindow sample. For information about samples, see Considerations for Installing SQL Server Samples and Sample Databases.
General Requirements for Switching Partitions
When a partition is transferred, the data is not physically moved; only the metadata about the location of the data changes. Before you can switch partitions, several general requirements must be met:
Both tables must exist before the SWITCH operation. The table from which the partition is being moved (the source table) and the table that is receiving the partition (the target table) must exist in the database before you perform the switch operation.
The receiving partition must exist and it must be empty. Whether you are adding a table as a partition to an already existing partitioned table, or moving a partition from one partitioned table to another, the partition that receives the new partition must exist and it must be an empty partition.
The receiving nonpartitioned table must exist and it must be empty. If you are reassigning a partition to form one nonpartitioned table, the table that receives the new partition must exist and it must be an empty nonpartitioned table.
Partitions must be on the same column. If you are switching a partition from one partitioned table to another, both tables must be partitioned on the same column.
Source and target tables must share the same filegroup. The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.
For information about partition switching when indexed views are defined, see Partition Switching When Indexed Views Are Defined.
Table and Index Structure Requirements
In addition to the general requirements above, both the source table and the target table must have the same structure. The structure requirements are as follows:
Source and target tables must have the same column structure and order. The tables must have the same columns with the same names and the same data type, length, collation, precision, scale, nullability, and PRIMARY KEY constraints (if any), and also have the same settings for ANSI_NULLS and QUOTED IDENTIFIER. Additionally, the columns must be defined in the same order. The IDENTITY property is not considered.
Warning
Partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want.
Nullability of the partitioning columns must match. The source and target tables must be either NULL or NOT NULL. If one of the tables is not partitioned, the nullability of the column corresponding to the partitioning column of the other table must match the column of the partitioned table.
Important
We recommend that you specify NOT NULL on the partitioning column of partitioned tables. We also recommend that you specify NOT NULL on nonpartitioned tables that are sources or targets for ALTER TABLE...SWITCH operations. When partitioned columns are NOT NULL, any CHECK constraints on partitioning columns are not forced to check for null values. Null values are ordinarily put in the left-most partition of a partitioned table. The absence of a NOT NULL constraint on the source and target tables might interfere with any CHECK constraints that are also defined on the partitioning column when switching any partition other than the left-most partition, and when the ANSI_NULLS database option is set to ON.
Computed columns must have the same syntax. If their corresponding partition keys are computed columns, the syntax of expressions that are defining their computed columns are the same, and both computed columns are persisted.
ROWGUID properties must be the same. Any column that is defined with the ROWGUID property must correspond to a column in the other table that is also defined with the ROWGUID property.
XML columns must have the same schema. Any xml columns must by typed to the same XML schema collection.
In-row settings of any text, ntext, or image columns must be the same. For more information about this setting, see In-Row Data.
Tables must have the same clustered indexes. Both source and target tables must have the same clustered indexes, and the indexes cannot be disabled before switching partitions.
Nonclustered indexes must be defined and must be identical. Any nonclustered indexes that are defined on the target table are also defined on the source table and are structured identically in terms of uniqueness, subkeys, and the sorting direction (ASC or DESC) for each index key column. Nonclustered indexes that are disabled are exempt from this requirement.
Constraint Requirements
The following additional constraint requirements must be satisfied to move partitions:
CHECK Constraints must be an exact match or applicable to both source and target. Any CHECK constraints that are defined on the target table must be defined on the source table either as an exact match, or as applicable (for example, as a subset) to the CHECK constraints of the target table.
Constraints on int columns must be the same or a subset. Any CHECK constraints on int columns in the source table must match or exist as a subset of constraints on the int column of the target table. For example, if the target table has a constraint on an int column Column1 that specifies Column1 < 100, the corresponding column Column1 of the source table must either have the same constraint or a subset of the constraint placed on values in the target table, such as Column1 < 90 of the source table. CHECK constraints that specify multiple columns must be defined by using the same syntax.
Nonpartitioned tables must have the same constraints as target partition. If you are adding a nonpartitioned table as a partition to an already existing partitioned table, there must be a constraint defined on the column of the source table that corresponds to the partition key of the target table. This makes sure that the range of values fits within the boundary values of the target partition.
Boundary values of the source partition must be within the boundary of target partition. If you are switching a partition of a partitioned table to another partitioned table, the boundary values of the source partition must fit within the boundary values of the target partition. If the boundary values do not fit, there must be a constraint defined on the partition key of the source table to make sure all the data in the table fits into the boundary values of the target partition.
Warning
Avoid data type conversion in constraint definitions. Constraints with implicit or explicit data type conversion that are defined on tables that are sources of partition switching may cause ALTER TABLE...SWITCH to fail.
Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted. For more information about this setting, see Guidelines for Disabling Indexes and Constraints. SQL Server applies any CASCADE rules that are defined on the foreign keys of the target table to the newly moved partition.
Additional Requirements for Moving Partitions
The following additional requirements must also be satisfied to move partitions:
Indexes must be aligned with table partitions. Any indexes on the source table must be aligned with the source table, Any indexes on the target table must be aligned with the target table. The source table and the target table can be both partitioned, or both non-partitioned or only one of the tables is partitioned. For more information about index alignment, see Special Guidelines for Partitioned Indexes.
Additional constraints and requirements apply to source tables with indexed views. If the target table in the ALTER TABLE … SWITCH statement has an indexed view defined, see Partition Switching When Indexed Views Are Defined for constraints and examples.
No full-text indexes allowed. There can be no full-text indexes on either the source table or the target table.
No XML indexes allowed on the target table. There can be no XML indexes on the target table.
No primary key/foreign keys defined if source table has the primary key. There can be no active primary key/foreign key relationship between the source table and the target table in which the source table holds the primary key.
No primary key/foreign keys defined if target table has the foreign key. There can be no active primary key/foreign key relationship between the source table and the target table in which the target table holds the foreign key.
No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.
Rules on source or target table are not permitted. There can be no rules defined on either the source table or the target table. CHECK constraints can be used on source and target tables.
Note
Rules are a backward compatibility feature. The preferred implementation is to use CHECK constraints. For CHECK constraint restrictions, see Constraint Requirements earlier in this topic.
Source and target tables must not be replicated. Neither the source table nor the target table can be sources of replication.
Must have required database permissions before partition switch. Because switching a partition uses an ALTER TABLE statement, you must have the required database permissions associated with the ALTER TABLE statement. The set of permissions does not have to be the same between the source table and the target table.
Triggers must not activate while moving partitions. No INSERT, UPDATE, or DELETE triggers or cascading actions are activated by moving table partitions, and it is not required for the source or target tables to have similarly defined triggers to move partitions.
Note
During an ALTER TABLE…SWITCH operation, a schema-modify lock is acquired on both the source and target tables to make sure that no other connections reference the tables during the change. For more information about locks, see Lock Modes.
To move table partitions