-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdbo.zsp_MonthlyRollForwardPartitionsOnce.StoredProcedure.sql
181 lines (157 loc) · 3.63 KB
/
dbo.zsp_MonthlyRollForwardPartitionsOnce.StoredProcedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
USE [MonthlyPartitionsTest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[zsp_MonthlyRollForwardPartitionsOnce]
@TableName nvarchar(128) = N'Test_Monthly'
, @WithTransaction bit = 0
as
/*
--单元测试
--重建分区表
--exec zsp_CreateSampleTable
--生成测试数据
--exec zsp_GenerateSampleMonthlyData '2052-6-2', 30
--滚动1次分区之前
--exec [zsp_TablesPartitionsInfoQuery]
--滚动1次分区
exec [zsp_MonthlyRollForwardPartitionsOnce]
--滚动1次分区之后
--exec [zsp_TablesPartitionsInfoQuery]
--查询Sample表分区中数据
exec zsp_SampleMonthlyDataPartitionsInfoQuery
--获取日期所在分区编号
--select dbo.SVF_GetMonthlyPartitionNo('Test_Monthly', '2026-01-01')
--获取日期所在按月独占分区编号 (-1: 该日期无该月独占分区, 0: 该日期已滚动过期, N: 该日期的独占按月分区编号)
--select dbo.SVF_GetMonthlyExclusivePartitionNo('Test_Monthly', '2026-01-01')
*/
begin
if (@WithTransaction = 1)
begin
set xact_abort on
begin transaction
end
declare @MergeDate date
declare @SplitDate date
declare @PartitionFunctionName varchar(128)
declare @PartitionSchemeName varchar(128)
declare @PartitionNo2FileGroupName nvarchar(64)
;WITH T
AS
(
select
*
from
iTVF_TablesPartitionsStorageInfo
(
@TableName
) a
)
SELECT
@MergeDate = cast(min(a.BoundaryValue) as date) --as [Merge]
, @SplitDate = cast(max(a.BoundaryValue) as date) --as [Split]
, @PartitionFunctionName = max(a.PartitionFunction)
, @PartitionSchemeName = max(a.PartitionScheme)
FROM
T a
where
a.TableName = @TableName
;WITH T
AS
(
select
*
from
iTVF_TablesPartitionsStorageInfo
(
@TableName
) a
)
SELECT
@PartitionNo2FileGroupName = a.FileGroupName
FROM
T a
where
a.TableName = @TableName
and
a.PartitionNumber = 2
select
@MergeDate as MergeDateOnHead
, @SplitDate as SplitDateOnTail
, DATEDIFF
(
MONTH
, @MergeDate
, @SplitDate
) as Months
, @PartitionNo2FileGroupName as Partition2FileGroupName
--=====================================================================
declare @SqlTemplate nvarchar(max)
declare @Sql nvarchar(max)
--=====================================================================
set @SqlTemplate = N'
TRUNCATE TABLE [%s] WITH (PARTITIONS (1 to 2))
'
set @Sql = FORMATMESSAGE
(
@SqlTemplate
, @TableName
)
print @sql
exec (@sql)
--=====================================================================
set @SqlTemplate = N'
ALTER PARTITION FUNCTION [%s]()
MERGE RANGE (''%s'')
'
set @Sql = FORMATMESSAGE
(
@SqlTemplate
, @PartitionFunctionName
, cast(@MergeDate as nvarchar(10))
)
print @sql
exec (@sql)
--=====================================================================
set @SqlTemplate = N'
ALTER PARTITION SCHEME [%s]
NEXT USED [%s]
'
set @Sql = FORMATMESSAGE
(
@SqlTemplate
, @PartitionSchemeName
, @PartitionNo2FileGroupName
)
print @sql
exec (@sql)
--=====================================================================
set @SqlTemplate = N'
ALTER PARTITION FUNCTION [%s]()
SPLIT RANGE (''%s'')
'
set @SplitDate = DATEADD(month, 1, @SplitDate)
set @Sql = FORMATMESSAGE
(
@SqlTemplate
, @PartitionFunctionName
, cast(@SplitDate as nvarchar(10))
)
print @sql
exec (@sql)
--=====================================================================
if (@WithTransaction = 1)
begin
commit transaction
end
--exec (@sql)
--ALTER PARTITION scheme ups_monthly ()
--next used ;
--ALTER PARTITION FUNCTION upf_monthly ()
--split RANGE (@);
--ALTER PARTITION FUNCTION upf_monthly ()
--merge RANGE ('2019-02-01');
end
GO