SQLServer 通用的分区增加和删除的算法

首先是将一个数据表加入到分区表的方法:
[dbo].[SP_Helper_Partition_Add] @SrcTable nvarchar(256),
待加入的表 @DestTable nvarchar(256), 目标表 @idxOnDest nvarchar(1024),
目标表上的索引创建语句 @Partition_func_name nvarchar(256), 分区函数
@PartCol nvarchar(256), 分区的列 @SonIsPart tinyint=1,
待加入的表是否分区 算法如下: 获取到源表的分区列的值;
在源表上加上分区列值的唯一性限制;
然后确定是否有一个分区恰好能存放这个表;如果不存在,则返回;
在源表上创建和目标表一样的索引;
现在源表和目标结构一致,源表的内容能够存在目标分区上,使用alter table
switch完成分区; 删除一个分区的算法
[dbo].[SP_Helper_Partition_Delete] @SrcTable nvarchar(256),
从该表中删除分区 @IdxOnTable nvarchar(1024), 该表上的索引创建语句
@Part_func nvarchar(256), 分区函数名 @PartCol nvarchar(256), 分区列
@PartIndex int, 待删除分区的索引 @IdxIsPart tinyint
索引是否在另一个分区函数上分区 算法如下:
首先根据源表的表结构复制一分临时表的表结构; 获取待删除分区的边界值;
在临时表上创建限制; 在这个表上创建索引; 将指定分区使用alter
switch交换到临时表上; 删除临时表

创建一个和目标表一样定义的表;
在表上加上constraint以确保表的数据在目标分区里; Alter table 源表 switch
to 目标表 partition 分区号
其中分区号可以通过$partition.partion_func获得 这个过程比insert
select要快很多,因为不涉及到io,只需要修改元数据,该边partition的onwer而已。
但是上述过程只适用于目标表的分区函数中目标分区已存在的情况。比如你定义了边界为1,2,3的分区函数,那么4和5都会插入到第4个分区里。
所以我推荐使用这种提前定义好分区函数所有分区的方法,如果你要按天分区,你就先定义好1000个分区。这种方法比较简单,不需要在加入数据时进行检查,从而导致错误。
如果您一定要使用动态分区的话,在加入一个源表作为新的分区时,需要如下操作:
首先取得新加入的分区列的值,设为X;
通过sys.Partition_range_values和sys.partition_functions找到目标分区函数所有的边界值;
进行如下的检查:
在这里分界值属于左边分区还是右边分区是有区别的,我们假设属于左边分区;
如果X存在于2步的边界值集合中,那么恭喜你,不需做额外的操作;
如果X大于2步中最大的边界,你不仅需要为X分裂出一个分区,还要保证小于X的数据都有自己分区。
每次加入分区时检查已有的边界值,如果不等于其中的任何一个,则以X为参数split,
并删除目标表中分区列和X相同的数据。
像之前描述的步骤那样switch源表到目标分区中。
如果在2步的时候,目标分区已经存在数据,alter
…switch语句就会失败,原因是目标分区不为空。
可以理解为,switch操作是修改表的元数据,用新的分配单元替换原来的分配单元。如果原来的分配单元不为空,那么这次替换就会造成一些数据的丢失,从而破坏了数据的完整性,因此是不允许的。

在SQL
Server中,对超级大表做数据归档,使用select和delete命令是十分耗费CPU时间和Disk空间的,SQL
Server必须记录相应数量的事务日志,而使用switch操作归档分区表的老数据,十分高效,switch操作不会移动数据,只是做元数据的置换,因此,执行分区切换操作的时间是非常短暂的,几乎是瞬间完成,但是,在做分区切换时,源表和靶表必须满足一定的条件:

-- 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]

ALTER TABLE
(Transact-SQL).aspx)

在创建Primary key 约束时,主键列是不可空的

3,主键约束

--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
--create unique clustered index
create unique clustered index ucix_dt_SwitchStaging_ID_Code
on dbo.dt_SwitchStaging(ID,Code)

澳门金沙vip 1澳门金沙vip 2

--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_澳门金沙vip,ID’ while the table ‘dbo.dt_SwitchStaging’ does
not have clustered index.

2,数据列的数据类型必须相同

alter table dbo.dt_partition
add constraint UQ__dt_partition_ID_Code
unique clustered(ID,Code)
alter table  dbo.dt_SwitchStaging
alter column ID bigint null

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

在靶表上创建聚集主键,switch成功

将分区表的第二个分区切换到靶表,SQL
Server抛出错误信息,要求靶表必须创建唯一聚集索引,注意,不是创建聚集主键;

在分区表上创建唯一聚集约束(unique clustered),在切换分区时,SQL
Server抛出错误消息,要求靶表必须创建唯一索引

对于Unknown值,Check约束认为逻辑结果是True,例如,check(ID>1 and
ID<10), 如果ID=Null,那么表达式ID>1 and ID<10
返回Unknown(或null),但是,Check约束返回的结果是True,即不违反check约束。

一,创建示例数据

二,源表和目标表的结构必须相同