Primary key 约束和unique 约束，在后台是使用unique index 来实现唯一语义的。
1，创建示例数据
-- 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] --insert data into dbo.dt_partition insert into dbo.dt_partition ( ID, Code ) values(15,2),(1,1)
2，在分区表上创建一个clustered index
--create clustered index create clustered index cix_dt_partition_ID on dbo.dt_partition(ID)
将partition 2 的数据swith out to staging table
--swith partition 2 to staging table alter table dbo.dt_partition switch partition 2 to dbo.dt_SwitchStaging
Msg 4913, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The table ‘DB_Study.dbo.dt_partition‘ has clustered index ‘cix_dt_partition_ID‘ while the table ‘DB_Study.dbo.dt_SwitchStaging‘ does not have clustered index.
在staging table上创建clustered index，switch 成功
--create clustered index create clustered index cix_dt_SwitchStaging_ID on dbo.dt_SwitchStaging(ID)
将staging table上的数据，swith into partition 2
--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) --switch staging table to partition 2 alter table dbo.dt_SwitchStaging switch to dbo.dt_partition partition 2 go
删除聚集索引和check 约束
--drop index drop index cix_dt_partition_ID on dbo.dt_partition --drop index drop index cix_dt_SwitchStaging_ID on dbo.dt_SwitchStaging --drop constraint alter table dbo.dt_SwitchStaging drop constraint CK__dt_SwitchStaging_ID
3，在partition 上创建nonclustered index
--create nonclustered index create nonclustered index ix_dt_partition_code on dbo.dt_partition(code) --create nonclustered index create nonclustered index ix_dt_SwitchStaging_code on dbo.dt_SwitchStaging(code) --swith partition 2 to staging table alter table dbo.dt_partition switch partition 2 to dbo.dt_SwitchStaging
将stging table中的数据 switch into partition 2
--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) --switch staging table to partition 2 alter table dbo.dt_SwitchStaging switch to dbo.dt_partition partition 2 go
删除非聚集索引和约束
--drop index
drop index ix_dt_partition_code
on dbo.dt_partition
--drop index
drop index ix_dt_SwitchStaging_code
on dbo.dt_SwitchStaging
--drop constraint
alter table dbo.dt_SwitchStaging
drop constraint CK__dt_SwitchStaging_ID
4，创建unique约束
alter table dbo.dt_partition add constraint UQ__dt_partition_ID_Code unique clustered(ID,Code) go
将partition 2的数据switch out to staging table
--swith partition 2 to staging table alter table dbo.dt_partition switch partition 2 to dbo.dt_SwitchStaging
Msg 4913, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. The table ‘DB_Study.dbo.dt_partition‘ has clustered index ‘UQ__dt_partition_ID_Code‘ while the table ‘DB_Study.dbo.dt_SwitchStaging‘ does not have clustered index.
在staging table上创建unique clustered index，而不是创建unique clustered 约束，switch 成功，
--create unique clustered index create unique clustered index ucix_dt_SwitchStaging_ID_Code on dbo.dt_SwitchStaging(ID,Code)
在staging table上创建unique clustered 约束，switch 成功，
--create constraint alter table dbo.dt_SwitchStaging add constraint UQ__dt_SwitchStaging_ID_Code unique clustered (ID,Code) go
unique 约束实际上是使用unique index来实现约束的。
5， 创建primary key约束
有余Primary key 约束，要求column 不能为null，又由于ID 是Partition column，不能使用alter table alter column修改其 nullability，所以删除，并重建表
--drop table drop table dbo.dt_partition go --create partitioned table create table dbo.dt_partition ( ID int not null, Code int ) on PS_int_Left (ID) go --drop table drop table dbo.dt_SwitchStaging GO --Create staging table create table dbo.dt_SwitchStaging ( ID int not null, Code int ) on [primary] go
为partition table创建聚集索引
alter table dbo.dt_partition add constraint PK__dt_partition_ID primary key clustered(ID) go
将partition 2 switch out to staging table
--swith partition 2 to staging table alter table dbo.dt_partition switch partition 2 to dbo.dt_SwitchStaging
Msg 4913, Level 16, State 1, Line 2
ALTER TABLE SWITCH statement failed. The table ‘DB_Study.dbo.dt_partition‘ has clustered index ‘PK__dt_partition_ID‘ while the table ‘DB_Study.dbo.dt_SwitchStaging‘ does not have clustered index.
在staging 上创建clustered index，switch 失败，失败的原因是unique clustered index 并不等同与primary key clustered，metadata不同，因为unique clustered index允许索引列为null，但是primary key 不允许为null。
--create unique clustered index create unique clustered index cix_dt_SwitchStaging_ID on dbo.dt_SwitchStaging(ID)
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table ‘DB_Study.dbo.dt_partition‘ for the index ‘cix_dt_SwitchStaging_ID‘ in target table ‘DB_Study.dbo.dt_SwitchStaging‘ .
创建primary key clustered ，swich 成功
--drop index
drop index cix_dt_SwitchStaging_ID
on dbo.dt_SwitchStaging
--add primary key clustered constraint
alter table dbo.dt_SwitchStaging
add constraint PK__dt_SwitchStaging_ID
primary key clustered(ID)
将staging table中的数据swith into partition 2
--add check constraint alter table dbo.dt_SwitchStaging with check add constraint CK__dt_SwitchStaging_ID check(ID >10 and ID<=20) --switch staging table to partition 2 alter table dbo.dt_SwitchStaging switch to dbo.dt_partition partition 2 go
注意：check 约束中没有使用 ID Is not null 条件，这是因为表在创建时，定义的partition column ：ID int not null
Partition switch条件3：All index 必须相同（index，primary key 和 unique）
原文：http://www.cnblogs.com/ljhdo/p/5040234.html