Partition: Partition switching (switch)

In SQL Server, data archiving of super Large tables, using SELECT and Delete commands is very CPU-intensive and disk space, SQL Server must record the corresponding number of transaction logs, and using the switch operation to archive partition table old data, very efficient, The switch operation does not move the data, only the replacement of the metadata, so the time to perform the partition switching operation is very short, almost instantaneous, but when partitioning the switch, the source and target tables must meet certain conditions:

• the structure of the table is the same : The data type of the column, the nullability (nullability) is the same;
• The index structure must be the same: the structure, aggregation, uniqueness of the index key, and the nullability of the column must be the same;
  • PRIMARY KEY constraint : If the source table has a PRIMARY key constraint, then the target table must create an equivalent primary KEY constraint;
  • UNIQUE constraint : A unique constraint can be implemented by using a unique index;
  • index key Structure : The order of index keys, including columns, uniqueness, aggregation must be the same;
• the same data space is stored: the source table and the target table must be created on the same filegroup or partition scheme;

Partition switching is a partition in the source table, switch to the target table (target_table), the target table can be a partitioned table, or not a partition table, the syntax of the switch operation is:

ALTER TABLE  [] to target_table  [ ]

One, create sample data

--Create Parition functionCreatePartitionfunctionPf_int_left (int) asRange Left  for Values(Ten, -);--create PARTITION SchemeCreatePartition Scheme Ps_int_left asPartition Pf_int_left All  to([Primary]);--Create partitioned TableCreate Tabledbo.dt_partition (IDint NULL, Codeint NULL) onps_int_left (ID)--Create Staging TableCreate Tabledbo.dt_switchstaging (IDint NULL, Codeint NULL) on [Primary]

View Code

Create a target table, dt_switchstaging, to store data for partitioned tables

Two, the structure of the source and target tables must be the same

1, the nullability of the data column must be the same (nullability)

Because the ID column of the target table is non-null (NOT NULL), the ID column of the source table is nullable (null) and the nullability is different, and SQL Server throws an error message when the partition is switched:

Alter Table dbo.dt_switchstaging Alter column int  not NULL ; -- Swith partition 2 to Staging table Alter Table  2 to Dbo.dt_switchstaging

ALTER TABLE SWITCH statement failed because column ' ID ' does not has the same nullability attribute in tables ' Dbo.dt_par Tition ' and ' dbo.dt_switchstaging '.

2, data columns must have the same data type

The data types of the source and target tables must be the same when performing partition switching, and SQL Server throws an error message, even if the data type is compatible:

Alter Table   dbo.dt_switchstagingaltercolumnbigintnull

ALTER TABLE SWITCH statement failed because column ' ID ' has the data type int in source TABLE ' Dbo.dt_partition ' which is diff Erent from it type bigint in target table ' dbo.dt_switchstaging '.

Three, implicit check constraints, implementing the nullable properties of a partition

The partition column (Partition column) allows the NULL,SQL server to have a null value as the minimum value when it is partitioned, storing it in the leftmost first partition with a Partition number of 1.

Any data with a NULL in the partition column would reside in the leftmost partition. NULL is considered smaller than the minimum value of the data type ' s values.

Partition functions (Partition function) define partition columns (Partition column) in each partition's value range, within SQL Server, the value range is implemented using a CHECK constraint, Each partition has a check constraint that defines the range of values for the partition column:

• Partition number=1,partition column allows null to exist;
• Other partition,partition column is not allowed to exist null;

For unknown values, the CHECK constraint considers the logical result to be true, for example, check (Id>1 and id<10), and if id=null, then the expression id>1 and id<10 returns unknown (or Null), However, the result returned by the check constraint is true, that is, the check constraint is not violated.

The index structure of the table must be the same, and uniqueness and aggregation must also be the same

The index structure, uniqueness, and aggregation of the table must be the same when performing a partition switch, and in SQL Server, the uniqueness of the unique constraint is implemented using the unique index.

1, the aggregation of indexes

Creates a clustered index on the partitioned table (clustered index), when switching partitions, SQL Server throws an error message that requires the target table to create a clustered index

-- Create clustered Index Create Clustered Index cix_dt_partition_id  on dbo.dt_partition (ID)

ALTER TABLE SWITCH statement failed. The table ' dbo.dt_partition ' has clustered index ' cix_dt_partition_id ' and the table ' dbo.dt_switchstaging ' does not hav e Clustered index.

2, UNIQUE constraint

Create a unique aggregation constraint on a partitioned table (unique clustered), when switching partitions, SQL Server throws an error message that requires the target table to create a unique index

Alter Table dbo.dt_partition Add constraint Uq__dt_partition_id_code Unique Clustered (Id,code)

ALTER TABLE SWITCH statement failed. The table ' dbo.dt_partition ' has clustered index ' uq__dt_partition_id_code ' while the table ' dbo.dt_switchstaging ' does no t have clustered index.

Workaround1: Create a unique clustered index on the target table (unique clustered) instead of creating a unique clustered constraint, switch succeeds;

-- create unique clustered index Create Unique Clustered Index Ucix_dt_switchstaging_id_code  on dbo.dt_switchstaging (Id,code)

Workaround2: Create a unique clustered constraint on the target table, switch succeeds;

3, PRIMARY KEY constraint

The primary key column is not nullable when you create the primary KEY constraint

--drop tableDrop Tabledbo.dt_partitionGoDrop Tabledbo.dt_switchstagingGO--Create partitioned TableCreate Tabledbo.dt_partition (IDint  not NULL, Codeint NULL,) onps_int_left (ID)Go--Create Staging TableCreate Tabledbo.dt_switchstaging (IDint  not NULL, Codeint NULL) on [Primary]Go

View Code

Create a PRIMARY KEY constraint for a partitioned table, using a unique clustered index (unique clustered) implementation, the only difference with a unique aggregation constraint is that the unique constraint column allows null

Alter Table dbo.dt_partition Add constraint pk__dt_partition_id Primary Key Clustered (ID)

The second partition of the partitioned table is switched to the target table, and SQL Server throws an error message that requires the target table to create a unique clustered index, noting that it is not creating a clustered primary key;

-- Swith partition 2 to Staging table Alter Table  2 to Dbo.dt_switchstaging

ALTER TABLE SWITCH statement failed. The table ' dbo.dt_partition ' has clustered index ' pk__dt_partition_id ' and the table ' dbo.dt_switchstaging ' does not hav e Clustered index.

Create a unique clustered index on the target table, SQL Server throws an error message when performing a partition switch: There is no equivalent index, because the clustered primary key creates an index that is unique, clustered, non-empty, and the unique clustered index is unique, clustered, and nullable, and the two are not exactly equivalent.

-- create unique clustered index Create Unique Clustered Index cix_dt_switchstaging_id  on dbo.dt_switchstaging (ID)

ALTER TABLE SWITCH statement failed. There is no identical index in source table ' dbo.dt_partition ' for the index ' cix_dt_switchstaging_id ' in target table ' db O.dt_switchstaging '.

Create a clustered primary key on the target table, switch succeeds

-- Add primary key clustered constraint Alter Table dbo.dt_switchstaging Add constraint pk__dt_switchstaging_id Primary Key Clustered (ID)

Five, summary

When performing partitioning operations, it is required that the source and target tables must meet:

• the structure of the table is the same : The data type of the column, the nullability (nullability) is the same;
• The index structure must be the same: the structure, aggregation, uniqueness of the index key, and the nullability of the column must be the same;
  • PRIMARY KEY constraint : If the source table has a PRIMARY key constraint, then the target table must create an equivalent primary KEY constraint;
  • UNIQUE constraint : A unique constraint can be implemented by using a unique index;
  • index key Structure : The order of index keys, including columns, uniqueness, aggregation must be the same;
• the same data space is stored: the source table and the target table must be created on the same filegroup or partition scheme;

Reference Documentation:

ALTER TABLE (Transact-SQL)

Partition: Partition switching (switch)