Partition：分區切換（Switch）

在SQL Server中，對超級大表做數據歸檔，使用select和delete命令是十分耗費CPU時間和Disk空間的，SQL Server必須記錄相應數量的事務日誌，而使用switch操作歸檔分區表的老數據，十分高效，switch操作不會移動數據，只是做元數據的置換，因此，執行分區切換操作的時間是非常短暫的，幾乎是瞬間完成，但是，在做分區切換時，源表和靶表必須滿足一定的條件：

• 表的結構相同：列的數據類型，可空性（nullability）相同；
• 索引結構必須相同：索引鍵的結構，聚集性，唯一性，列的可空性必須相同；
  • 主鍵約束：如果源表存在主鍵約束，那麼靶表必須創建等價的主鍵約束；
  • 唯一約束：唯一約束可以使用唯一索引來實現；
  • 索引鍵的結構：索引鍵的順序，包含列，唯一性，聚集性都必須相同；
• 存儲的數據空間（data space）相同：源表和靶表必須創建在相同的FileGroup或Partition Scheme上；

分區切換是將源表中的一個分區，切換到靶表（target_table）中，靶表可以是分區表，也可以不是分區表，switch操作的語法是：

ALTER TABLE schema_name . table_name 
SWITCH [ PARTITION source_partition_number_expression ]
TO target_table  [ PARTITION target_partition_number_expression ]

一，創建示例數據

-- 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
all to ([primary]);

--create partitioned table
create table dbo.dt_partition
(
ID int null,
Code int null
)
on ps_int_left (id)

--Create staging table
create table dbo.dt_SwitchStaging
(
ID int null,
Code int null
)
on [primary]

創建靶表 dt_SwitchStaging，用於存儲分區表的數據

二，源表和目標表的結構必須相同

1，數據列的可空性必須相同（nullability）

由於靶表的ID列是非空的（not null），源表的ID列是可空的（null），可空性不同，在切換分區時，SQL Server會拋出錯誤消息：

alter table dbo.dt_SwitchStaging
alter column ID int not null;

--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2 
to dbo.dt_SwitchStaging

ALTER TABLE SWITCH statement failed because column 'ID' does not have the same nullability attribute in tables 'dbo.dt_partition' and 'dbo.dt_SwitchStaging'.

2，數據列的數據類型必須相同

在執行分區切換時，源表和靶表的數據類型必須相同，即使數據類型相相容，SQL Server會拋出錯誤消息：

alter table  dbo.dt_SwitchStaging
alter column ID bigint null

ALTER TABLE SWITCH statement failed because column 'ID' has data type int in source table 'dbo.dt_partition' which is different from its type bigint in target table 'dbo.dt_SwitchStaging'.

三，隱式的Check約束，實現分區的可空屬性

分區列（Partition Column）允許為NULL，SQL Server在分區時，將NULL值作為最小值，存儲在最左邊的第一個分區中，其Partition Number是1。

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

分區函數（Partition Function）定義分區列（Partition Column）在每一個分區的取值區間（Value Range），在SQL Server內部，取值區間是使用Check約束來實現的，每一個Partition都有一個check 約束，用於限定Partition column的取值範圍：

• Partition Number=1，Partition column允許存在null；
• 其他Partition，Partition column不允許存在null；

對於Unknown值，Check約束認為邏輯結果是True，例如，check(ID>1 and ID<10)， 如果ID=Null，那麼表達式ID>1 and ID<10 返回Unknown（或null），但是，Check約束返回的結果是True，即不違反check約束。

四，表的索引結構必須相同，唯一性和聚集性也必須相同

在執行分區切換時，表的索引結構，唯一性和聚集性必須相同，在SQL Server中，使用unique index 實現unique 約束的唯一性。

1，索引的聚集性

在分區表上創建一個聚集索引（clustered index），在切換分區時，SQL Server拋出錯誤信息，要求靶表必須創建聚集索引

--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' while the table 'dbo.dt_SwitchStaging' does not have clustered index.

2，唯一約束

在分區表上創建唯一聚集約束（unique clustered），在切換分區時，SQL Server拋出錯誤消息，要求靶表必須創建唯一索引

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 not have clustered index.

Workaround1：在靶表上創建唯一聚集索引（unique clustered），而不是創建unique clustered 約束，switch 成功；

--create unique clustered index
create unique clustered index ucix_dt_SwitchStaging_ID_Code
on dbo.dt_SwitchStaging(ID,Code)

Workaround2：在靶表上創建unique clustered 約束，switch 成功；

3，主鍵約束

在創建Primary key 約束時，主鍵列是不可空的

--drop table
drop table dbo.dt_partition
go
drop table dbo.dt_SwitchStaging
GO
--create partitioned table
create table dbo.dt_partition
(
ID int not null,
Code int null,
)
on PS_int_Left (ID)
go
--Create staging table
create table dbo.dt_SwitchStaging
(
ID int not null,
Code int null
)
on [primary]
go

為分區表創建主鍵約束，使用唯一聚集索引（unique clustered）實現，跟唯一聚集約束的唯一區別是唯一約束列允許為NULL

alter table dbo.dt_partition
add constraint PK__dt_partition_ID
primary key clustered(ID)

將分區表的第二個分區切換到靶表，SQL Server拋出錯誤信息，要求靶表必須創建唯一聚集索引，註意，不是創建聚集主鍵；

--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2 
to dbo.dt_SwitchStaging

ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'PK__dt_partition_ID' while the table 'dbo.dt_SwitchStaging' does not have clustered index.

在靶表上創建唯一聚集索引，在執行分區切換時，SQL Server拋出錯誤消息：沒有等價的索引，這是因為聚集主鍵創建的索引是唯一的，聚集的，非空的，而唯一聚集索引是唯一的，聚集的，可空的，兩者不是完全等價的。

--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 'dbo.dt_SwitchStaging' . 

在靶表上創建聚集主鍵，switch成功

--add primary key clustered constraint
alter table dbo.dt_SwitchStaging
add constraint PK__dt_SwitchStaging_ID
primary key clustered(ID)

五，總結

在執行分區操作時，要求源表和靶表必須滿足：

• 表的結構相同：列的數據類型，可空性（nullability）相同；
• 索引結構必須相同：索引鍵的結構，聚集性，唯一性，列的可空性必須相同；
  • 主鍵約束：如果源表存在主鍵約束，那麼靶表必須創建等價的主鍵約束；
  • 唯一約束：唯一約束可以使用唯一索引來實現；
  • 索引鍵的結構：索引鍵的順序，包含列，唯一性，聚集性都必須相同；
• 存儲的數據空間（data space）相同：源表和靶表必須創建在相同的FileGroup或Partition Scheme上；

參考文檔：

ALTER TABLE (Transact-SQL)