使用Switch 能够将分区交换到一个empty table中，swith是metadata-only operation，不会物理上移动数据，因此switch操作能够瞬间完成。
Switch Command的作用是在做 range split 和 range merge时，避免 data movement，使range split和 range merge都在empty partition上操作。
一，示例
1，创建分区表和staging表，Staging表和分区表的所有Partition在相同FileGroup上创建。
-- create parition function CREATE PARTITION FUNCTION pf_int_Left (int) AS RANGE LEFT FOR VALUES (10,20); --create partition scheme CREATE PARTITION SCHEME PS_int_Left AS PARTITION pf_int_Left TO ([primary], [primary], [primary]); --create partitioned table create table dbo.dt_partition ( ID int, Code int ) on PS_int_Left (ID) --Create staging table create table dbo.dt_SwitchStaging ( ID int, Code int ) on [primary]
2，将分区表某一个分区的数据 和 staging table switch
switch out to staging table
--insert data into dbo.dt_partition insert into dbo.dt_partition ( ID, Code ) values(15,2) --swith partition 2 to staging table alter table dbo.dt_partition switch partition 2 to dbo.dt_SwitchStaging
switch staging table out to partition 2
--switch staging table to partition 2 alter table dbo.dt_SwitchStaging switch to dbo.dt_partition partition 2
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘DB_Study.dbo.dt_SwitchStaging‘ allow values that are not allowed by range defined by partition 2 on target table ‘DB_Study.dbo.dt_partition‘.
意思是缺少一个check constraint，保证staging table中的数据满足 Partiton Range的要求。Partition 2的range 是PartitionColumn>10 and PartitionColumn<=20，使用check constraint 满足Partition 2的range要求。
--add check constraint alter table dbo.dt_SwitchStaging with check add constraint CK__dt_SwitchStaging_ID check(ID >10 and ID<=20)
当执行 switch时，依然失败，错误原因是 对Null的处理方式不同。在Partition 中，Null值是最小的，处于Partition Number=1的分区中，但是在Check 约束中，如果插入ID的数据存在null，Check 约束对Unknown的处理方式True。这就导致Null值能够插入到dbo.dt_SwitchStaging，而 Partition Number=2是不会存在Null值的，所以Check 约束不满足Partition Number=2的value Range。
--add check constraint alter table dbo.dt_SwitchStaging with check add constraint CK__dt_SwitchStaging_ID check(ID >10 and ID<=20 and ID is not null)
3，Partition Number=1的分区允许Null值
-- drop check constraint alter table dbo.dt_SwitchStaging drop constraint CK__dt_SwitchStaging_ID --add check constraint alter table dbo.dt_SwitchStaging with check add constraint CK__dt_SwitchStaging_ID check(ID <=10) -- insert into data to partition 1 insert into dbo.dt_partition (ID,Code) values(1,1)
经过测试，Partition Number=1的分区允许Null值。
--switch patition 1 out to dbo.dt_SwitchStaging alter table dbo.dt_partition switch partition 1 to dbo.dt_SwitchStaging --insert null to dbo.dt_SwitchStaging insert into dbo.dt_SwitchStaging (ID,Code) values(null,1) --swith dbo.dt_SwitchStaging into patition 1 alter table dbo.dt_SwitchStaging switch to dbo.dt_partition partition 1
4，Partition switch的示例
参考：Partition table的switch条件1：结构相同（类型，nullability）
Partition table的switch条件2：Partition 的隐式Check约束 和Nullability
Partition switch条件3：All index 必须相同（index，primary key 和 unique）
二，使用Alter Table Switch partition
Syntax
ALTER TABLE [schema_name . ] table_name SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ] [ WITH ( <low_lock_priority_wait> ) ]
<low_priority_lock_wait>::=
{
WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}
SWITCH [ PARTITION source_partition_number_expression ]
TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ]
Switches a block of data in one of the following ways:
Reassigns all data of a table as a partition to an already-existing partitioned table.
Switches a partition from one partitioned table to another.
Reassigns all data in one partition of a partitioned table to an existing non-partitioned table.
If table is a partitioned table, source_partition_number_expression must be specified.
If target_table is partitioned, target_partition_number_expression must be specified.
If reassigning a table‘s data as a partition to an already-existing partitioned table, or switching a partition from one partitioned table to another, the target partition must exist and it must be empty.
If reassigning one partition‘s data to form a single table, the target table must already be created and it must be empty. Both the source table or partition, and the target table or partition, must reside in the same filegroup. The corresponding indexes, or index partitions, must also reside in the same filegroup. Many additional restrictions apply to switching partitions. table and target_table cannot be the same. target_table can be a multi-part identifier.
source_partition_number_expression and target_partition_number_expression are constant expressions that can reference variables and functions. These include user-defined type variables and user-defined functions. They cannot reference Transact-SQL expressions.
A partitioned table with a clustered columstore index behaves like a partitioned heap:
The primary key must include the partition key.
A unique index must include the partition key. Note that including the partition key to an existing unique index can change the uniqueness.
In order to switch partitions, all non-clustered indexes must include the partition key.
Nonclustered columnstore indexes built for SQL Server 2016 CTP1, and for SQL Database before version V12 were in a read-only format. Nonclustered columnstore indexes must be rebuilt to the current format (which is updatable) before any PARTITION operations can be performed.
WAIT_AT_LOW_PRIORITY
MAX_DURATION = time [MINUTES ]
The wait time (an integer value specified in minutes) that the SWITCH or online index rebuild locks will wait with low priority when executing the DDL command. If the operation is blocked for the MAX_DURATION time, one of the ABORT_AFTER_WAIT actions will be executed. MAX_DURATION time is always in minutes, and the word MINUTES can be omitted.
ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]
Kill all user transactions that block currently the SWITCH or online index rebuild DDL operation so that the operation can continue.
Requires ALTER ANY CONNECTION permission.
Conditionally drops the column or constraint only if it already exists.
If a partitioned table uses a partition function (by referencing a partition scheme), the SPLIT and MERGE operations on the partition function cause SQL Server to redistribute relevant table data among any nonempty partitions involved. For the SPLIT operation, partitioned data and indexes are redistributed between an existing partition and a new one, and for MERGE, the data and indexes are consolidated from two partitions into the remaining one. For a partitioned table with a significant amount of data, this data movement can be costly and time-consuming.
To avoid data movement, SQL Server provides a SWITCH option on the ALTER TABLE statement. The SWITCH operation allows you manipulate the partitioned table so that SPLIT and MERGE will only operate on empty partitions, resulting in metadata-only operations. Resulting SPLIT and MERGE operations then become simply metadata operations and occur instantly. The SWITCH statement itself is a metadata-only operation, so it also happens almost immediately.
The SWITCH option is the key feature of table partitioning that makes management of large tables much more feasible. You can use the SWITCH option to take a partition‘s data and aligned indexes out of the partitioned table in order to manipulate the data, and then put it back in without affecting the partitioned table. You can take neighboring partitions out to combine data into one staging table, merge the two empty partitions, and then switch the data back into the partitioned table as a single partition. Perhaps most useful is that you can move the partitions forward in time by switching out the oldest partition and switching in one with fresher data. An effective strategy for aging data is the sliding window scenario.
The word SWITCH implies a symmetrical exchange, but the ALTER TABLE SWITCH option requires a TO clause that gives it a direction. You ALTER the table that has the source data, and SWITCH its partition (if it has one) TO a target table (and its partition if it has one.) In all cases, the FROM object (partition or stand-alone table) can have data, but the TO object must be empty.
To use the SWITCH option, at least one of the two tables involved must be partitioned using a partition scheme. It‘s common to refer to the stand-alone nonpartitioned table as a staging table. There are essentially three ways to use the SWITCH option:
There are numerous requirements for the SWITCH command. For example, the switched tables must have the same column structure and indexes, even the same foreign key constraints. All the tables must exist and the destination table or partition must be empty. If you are switching between two partitioned tables, the partitions must use the same partitioned column. Finally, the source and targets of the switch must exist on the same filegroup. Being on the same filegroup and in the same database, the SWITCH operation can avoid any data movement.
There are also some important requirements for indexes during a SWITCH operation:
Nonaligned indexes can be disabled for the duration of the SWITCH and re-enabled afterwards. There are additional requirements pertaining to indexes and partitioned tables, in particular XML and full-text indexes. For more information, see "Special Guidelines for Partitioned Indexes" in SQL Server 2008 Books Online at http://msdn.microsoft.com/en-us/library/ms187526.aspx.
Note Whenever you switch data into or out of a partitioned table, it is important to update statistics on the table. A partitioned table keeps statistics at the table, not the partition level. If you manually update statistics on a static fact table after each new partition is loaded, you can turn off automatic statistics updating.
When you switch from a partitioned table‘s partition to an empty stand-alone table, the partition‘s data and the aligned index‘s partition will belong to the staging table. Only the metadata that governs which table the data and indexes belong to has changed.
参考文档：
https://msdn.microsoft.com/en-us/library/dd578580.aspx
http://www.cnblogs.com/biwork/archive/2012/12/07/BIWORK_Table_Partition_Notes.html
原文：http://www.cnblogs.com/ljhdo/p/5037033.html