Share via

Transferring Data Efficiently by Using Partition Switching

  • Article

You can use the Transact-SQL ALTER TABLE...SWITCH statement to quickly and efficiently transfer blocks of 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, see Designing Partitions to Manage Subsets of Data. To see sample code that implements partition switching, see Readme_SlidingWindow.

General Requirements for Switching Partitions

When transferring a partition, you are not physically moving the data. You are only changing metadata about where the data is stored. This means that both 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.

If 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 receiving partition must exist and it must be empty.

If you are reassigning a partition to form one nonpartitioned table, the table that is receiving the partition must already be created and it must be empty.

If you are switching a partition from one partitioned table to another, both tables must be partitioned on the same column.

Both the source and the target 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, or index partitions, must also reside in the same filegroup. However, the filegroup can be different from that of the corresponding tables or other corresponding indexes.

Table and Index Structure Requirements

Both the source table and the target table must have the same structure. This means the following:

  • 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, 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

    Performing 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.

  • The nullability of their partitioning columns must agree. That is, both must be either NULL or NOT NULL. If one of the tables is not partitioned, the nullability of the column corresponding to the other table's partitioning column must be in agreement with the other table's partitioning column.

    Important

    We recommend that you specify NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets for ALTER TABLE...SWITCH operations. By doing this, you make sure that any CHECK constraints on partitioning columns do not have to check for null values. Null values are ordinarily placed in the leftmost partition of a partitioned table. When you are switching any partition other than the leftmost partition and when the ANSI_NULLS database option is set to ON, 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.

  • 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.

  • 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.

  • Any xml columns must by typed to the same XML schema collection.

  • The in-row settings of any text, ntext, or image columns are the same. For more information about this setting, see In-Row Data.

  • The tables have the same clustered indexes. These indexes cannot be disabled.

  • 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

Any CHECK constraints that are defined on the target table are also defined on the source table either as an exact match, or as applicable to the CHECK constraint 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 constraint whose values are a subset of the constraint of the target table, such as Column1 < 90. CHECK constraints that specify multiple columns must be defined by using the same syntax.

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.

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 that include implicit or explicit data type conversion and that are defined on tables that are sources of partition switching, may cause ALTER TABLE...SWITCH to fail.

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. 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:

  • Any indexes on either the source table or the target table must be aligned with the table, whether the source table or the target table is partitioned, or both tables are partitioned.

  • There can be no full-text indexes on either the source table or the target table.

  • There can be no XML indexes on the target table.

  • 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.

  • 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.

  • The source table cannot be referenced by a foreign key in another table.

  • The source table and the target table cannot participate in a view with schema binding. Therefore, indexed views cannot be defined on them.

  • There can be no rules defined on either the source table or the target table.

    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.

  • Neither the source table nor the target table can be sources of replication.

  • A partition switch involves execution of an ALTER TABLE statement. Therefore, 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.

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

By performing an ALTER TABLE…SWITCH operation, a schema-modify lock is acquired on both the source and target tables to make sure no other connections reference even the metadata for the tables during the change. For more information about locks, see Lock Modes.

To move table partitions

See Also

Concepts

Designing Partitions to Manage Subsets of Data

Other Resources

DBCC CHECKIDENT (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
Readme_SlidingWindow

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:

Added a Note that recommends specifying NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets for ALTER TABLE...SWITCH operations.

14 April 2006
Changed content:
  • XML indexes that are defined on the source table do not affect partition switching. There can be no XML indexes on the target table to switch partitions.