CREATE PROCEDURE [dbo].[sp_SlideWindow]
@SplitRange SMALLDATETIME -- 指定活动表新增分区的边界
AS
BEGIN
DECLARE @SwitchRange SMALLDATETIME
DECLARE @MergeRange SMALLDATETIME
--获得活动表、存档表合并分区以及存档表的新分区的边界
SELECT @MergeRange=CONVERT(SMALLDATETIME,value) FROM sys.partition_range_values, sys.partition_functions WHERE sys.partition_functions.function_id = sys.partition_range_values.function_id AND sys.partition_functions.name = 'E_Alive_Partition_Func' AND boundary_id = 1
SELECT @SwitchRange=CONVERT(SMALLDATETIME,value) FROM sys.partition_range_values, sys.partition_functions WHERE sys.partition_functions.function_id = sys.partition_range_values.function_id AND sys.partition_functions.name = 'E_Alive_Partition_Func' AND boundary_id = 2
BEGIN TRANSACTION
ALTER PARTITION SCHEME [E_Alive_Partition_Schema] NEXT USED [PRIMARY]
ALTER PARTITION SCHEME [E_Staging_Partition_Schema] NEXT USED [PRIMARY]
--在活动表中新增分区
ALTER PARTITION FUNCTION [E_Alive_Partition_Func]()SPLIT RANGE(CONVERT(NVARCHAR,@SplitRange,120))
--在存档表中新增分区
ALTER PARTITION FUNCTION [E_Staging_Partition_Func]()SPLIT RANGE(CONVERT(NVARCHAR,@SwitchRange ,120))
--切换分区
ALTER TABLE [Alive_Table] SWITCH PARTITION 2 TO [Staging_Table] PARTITION 2
--合并活动表分区与存档表分区
ALTER PARTITION FUNCTION [E_Alive_Partition_Func]() MERGE RANGE(CONVERT(NVARCHAR,@MergeRange,120))
ALTER PARTITION FUNCTION [E_Staging_Partition_Func]() MERGE RANGE(CONVERT(NVARCHAR,@MergeRange,120))
COMMIT TRANSACTION
END