根据开发需要写了个分区脚本，希望以后开发人员不用特意为一个表单独写分区脚本
这里写的是一个 小时分区脚本
一天一个文件组，一天一个分区文件，一天24个分区
由于不经常写数据库方面的东西，所以发布到这里，希望大家帮帮找点问题。
下面为初始化部分：
代码
1 Declare @DbName varchar(50) = 'AAbis', --数据库名
2 @tbName varchar(50) = 'Part1', -- 表名
3 @PartitionColumn varchar(50) = 'CallTime', -- 分区字段
4 @IndexType varchar(50) = 'clustered', -- 索引类型(nonclustered|clustered)
5 @KeyColumn varchar(100) = 'ID,CallTime', --主键字段
6 @folderPath varchar(200) = 'Part\', -- 存储位置(每天一个文件24个分区)
7 @execInterval int = 0 -- 执行间隔（提前几天执行）
8
9
10 --定义变量
11 Declare @FilePath varchar(250),
12 @FGName varchar(50),@FGSql varchar(500), --定义分组
13 @PFileName varchar(50),@PFileSql varchar(500), --定义分区文件
14 @PFName varchar(50),@PFSql varchar(500), --定义分区函数
15 @PSName varchar(50),@PSSql varchar(500), --定义分区架构
16 @DelDataSql varchar(500)
17
18 --建立新分区执行时间设置
19 Declare @dtDay datetime, --执行什么时间的数据
20 @dtDayStr varchar(50), -- 时间串
21 @dtShort varchar(50), -- 年月日时间字符
22 @hourNum int=0,
23 @tempHourStr varchar(10)
24
25 set @dtDay = dateadd(Day,@execInterval,getdate())
26 set @dtDayStr = ltrim(Str(YEAR(@dtDay)))+LTRIM(str(month(@dtDay)))+LTRIM(str(day(@dtDay)))
27 set @dtShort = CONVERT(varchar(100), @dtDay, 111)
28
29 --指定数据库路径
30 set @folderPath=AAbis.dbo.GetPartitionPath(@dtDay)+@folderPath
31
32 --建立分组（每天建立一个分组）
33 set @FGName = 'FG_'+@tbName +@dtDayStr
34 set @FGSql = 'ALTER DATABASE '+@DbName+' ADD FILEGROUP '+ @FGName
35 exec(@FGSql)
36
37 --创建文件（每天建立一个文件）
38 set @PFileName = 'PF_'+@tbName +@dtDayStr
39 set @FilePath = @folderPath+@tbName +@dtDayStr+'.NDF'
40 set @PFileSql = 'ALTER DATABASE '+@DbName+' ADD FILE (NAME = '''+@PFileName+''', FILENAME = '''+@FilePath+''') TO FILEGROUP '+@FGName
41 exec(@PFileSql)
42
43 --创建分区函数
44 set @PFName = 'PF_'+@tbName --.dbo.@DbName+
45 set @tempHourStr = Ltrim(str(@hourNum))
46 if(LEN(@tempHourStr)=1)
47 begin
48 set @tempHourStr = '0'+ @tempHourStr
49 end
50 --判断分区函数
51 --drop partition function PF_Part_Table --删除分区函数
52 set @PFSql = 'USE '+@DbName+' CREATE PARTITION FUNCTION '+@PFName+'(DateTime)
53 AS
54 RANGE LEFT FOR VALUES ('''+@dtShort+' '+@tempHourStr+':59:59'')'
55 --select @PFSql
56 exec(@PFSql)
57
58 --创建分区方案
59 set @PSName = 'PS_'+@tbName
60 --判断分区方案
61 --drop partition scheme PS_Part_Table --删除分区架构
62 set @PSSql ='USE '+@DbName+' Create PARTITION SCHEME '+@PSName+'
63 AS PARTITION '+@PFName+' TO ('+@FGName+','+@FGName+')'
64 --select @PSSql
65 exec(@PSSql)
66
67 --建表
68 declare @newTbSql varchar(500)
69 set @newTbSql ='Create Table '+@DbName+'.dbo.'+@tbName+'
70 (
71 ID INT NOT NULL IDENTITY(1,1),
72 CallTime DATETIME NOT NULL
73 ) ON PS_'+@tbName+'('+@PartitionColumn+')'
74 exec(@newTbSql)
75
76 declare @historyTbSql varchar(500)
77 set @historyTbSql ='Create Table '+@DbName+'.dbo.'+@tbName+'_History
78 (
79 ID INT NOT NULL IDENTITY(1,1),
80 CallTime DATETIME NOT NULL
81 ) ON PS_'+@tbName+'('+@PartitionColumn+')'
82 exec(@historyTbSql)
83
84 -- 创建索引
85 if(@IndexType<>'')
86 begin
87 declare @IndexSql varchar(500)
88 set @IndexSql = 'create '+@IndexType+' index index_'+@tbName+' on '+@DbName+'.dbo.'+@tbName+'('+@PartitionColumn+') on
89
90 PS_'+@tbName+'('+@PartitionColumn+')'
91 exec(@IndexSql)
92 end
93 --为分区表设置主键
94 if(@KeyColumn<>'')
95 begin
96 declare @SetKeySql varchar(500)
97 set @SetKeySql =' alter table '+@DbName+'.dbo.'+@tbName+' add constraint PK_'+@tbName+' primary key ('+@KeyColumn+')'
98 exec(@SetKeySql)
99 end
100
101 --初始化测试数据
102 declare @DT datetime,@testInsSql varchar(500)
103 SELECT @DT = '2010-06-30 00:59:59'
104 WHILE (@DT <= '2010-06-30 01:59:59')
105 BEGIN
106 set @testInsSql = 'INSERT INTO '+@DbName+'.dbo.'+@tbName+' VALUES('''+CONVERT(varchar(100), @DT, 21)+''')'
107 --SELECT @testInsSql
108 exec(@testInsSql)
109 SET @DT=dateadd(Hour,1,@DT)
110 END
111
112 --查询
113 --SELECT *,$PARTITION.PF_Part1(Calltime) as PartitionId FROM AAbis.dbo.Part1--查询分区号
114 --SELECT * FROM AAbis.dbo.Part1 WHERE $PARTITION.PF_Part1(Calltime) = 1 --查询分区数据
115 --select * from sys.partition_functions
116 --select * from sys.partition_range_values
117 --select * from sys.partition_schemes
2 @tbName varchar(50) = 'Part1', -- 表名
3 @PartitionColumn varchar(50) = 'CallTime', -- 分区字段
4 @IndexType varchar(50) = 'clustered', -- 索引类型(nonclustered|clustered)
5 @KeyColumn varchar(100) = 'ID,CallTime', --主键字段
6 @folderPath varchar(200) = 'Part\', -- 存储位置(每天一个文件24个分区)
7 @execInterval int = 0 -- 执行间隔（提前几天执行）
8
9
10 --定义变量
11 Declare @FilePath varchar(250),
12 @FGName varchar(50),@FGSql varchar(500), --定义分组
13 @PFileName varchar(50),@PFileSql varchar(500), --定义分区文件
14 @PFName varchar(50),@PFSql varchar(500), --定义分区函数
15 @PSName varchar(50),@PSSql varchar(500), --定义分区架构
16 @DelDataSql varchar(500)
17
18 --建立新分区执行时间设置
19 Declare @dtDay datetime, --执行什么时间的数据
20 @dtDayStr varchar(50), -- 时间串
21 @dtShort varchar(50), -- 年月日时间字符
22 @hourNum int=0,
23 @tempHourStr varchar(10)
24
25 set @dtDay = dateadd(Day,@execInterval,getdate())
26 set @dtDayStr = ltrim(Str(YEAR(@dtDay)))+LTRIM(str(month(@dtDay)))+LTRIM(str(day(@dtDay)))
27 set @dtShort = CONVERT(varchar(100), @dtDay, 111)
28
29 --指定数据库路径
30 set @folderPath=AAbis.dbo.GetPartitionPath(@dtDay)+@folderPath
31
32 --建立分组（每天建立一个分组）
33 set @FGName = 'FG_'+@tbName +@dtDayStr
34 set @FGSql = 'ALTER DATABASE '+@DbName+' ADD FILEGROUP '+ @FGName
35 exec(@FGSql)
36
37 --创建文件（每天建立一个文件）
38 set @PFileName = 'PF_'+@tbName +@dtDayStr
39 set @FilePath = @folderPath+@tbName +@dtDayStr+'.NDF'
40 set @PFileSql = 'ALTER DATABASE '+@DbName+' ADD FILE (NAME = '''+@PFileName+''', FILENAME = '''+@FilePath+''') TO FILEGROUP '+@FGName
41 exec(@PFileSql)
42
43 --创建分区函数
44 set @PFName = 'PF_'+@tbName --.dbo.@DbName+
45 set @tempHourStr = Ltrim(str(@hourNum))
46 if(LEN(@tempHourStr)=1)
47 begin
48 set @tempHourStr = '0'+ @tempHourStr
49 end
50 --判断分区函数
51 --drop partition function PF_Part_Table --删除分区函数
52 set @PFSql = 'USE '+@DbName+' CREATE PARTITION FUNCTION '+@PFName+'(DateTime)
53 AS
54 RANGE LEFT FOR VALUES ('''+@dtShort+' '+@tempHourStr+':59:59'')'
55 --select @PFSql
56 exec(@PFSql)
57
58 --创建分区方案
59 set @PSName = 'PS_'+@tbName
60 --判断分区方案
61 --drop partition scheme PS_Part_Table --删除分区架构
62 set @PSSql ='USE '+@DbName+' Create PARTITION SCHEME '+@PSName+'
63 AS PARTITION '+@PFName+' TO ('+@FGName+','+@FGName+')'
64 --select @PSSql
65 exec(@PSSql)
66
67 --建表
68 declare @newTbSql varchar(500)
69 set @newTbSql ='Create Table '+@DbName+'.dbo.'+@tbName+'
70 (
71 ID INT NOT NULL IDENTITY(1,1),
72 CallTime DATETIME NOT NULL
73 ) ON PS_'+@tbName+'('+@PartitionColumn+')'
74 exec(@newTbSql)
75
76 declare @historyTbSql varchar(500)
77 set @historyTbSql ='Create Table '+@DbName+'.dbo.'+@tbName+'_History
78 (
79 ID INT NOT NULL IDENTITY(1,1),
80 CallTime DATETIME NOT NULL
81 ) ON PS_'+@tbName+'('+@PartitionColumn+')'
82 exec(@historyTbSql)
83
84 -- 创建索引
85 if(@IndexType<>'')
86 begin
87 declare @IndexSql varchar(500)
88 set @IndexSql = 'create '+@IndexType+' index index_'+@tbName+' on '+@DbName+'.dbo.'+@tbName+'('+@PartitionColumn+') on
89
90 PS_'+@tbName+'('+@PartitionColumn+')'
91 exec(@IndexSql)
92 end
93 --为分区表设置主键
94 if(@KeyColumn<>'')
95 begin
96 declare @SetKeySql varchar(500)
97 set @SetKeySql =' alter table '+@DbName+'.dbo.'+@tbName+' add constraint PK_'+@tbName+' primary key ('+@KeyColumn+')'
98 exec(@SetKeySql)
99 end
100
101 --初始化测试数据
102 declare @DT datetime,@testInsSql varchar(500)
103 SELECT @DT = '2010-06-30 00:59:59'
104 WHILE (@DT <= '2010-06-30 01:59:59')
105 BEGIN
106 set @testInsSql = 'INSERT INTO '+@DbName+'.dbo.'+@tbName+' VALUES('''+CONVERT(varchar(100), @DT, 21)+''')'
107 --SELECT @testInsSql
108 exec(@testInsSql)
109 SET @DT=dateadd(Hour,1,@DT)
110 END
111
112 --查询
113 --SELECT *,$PARTITION.PF_Part1(Calltime) as PartitionId FROM AAbis.dbo.Part1--查询分区号
114 --SELECT * FROM AAbis.dbo.Part1 WHERE $PARTITION.PF_Part1(Calltime) = 1 --查询分区数据
115 --select * from sys.partition_functions
116 --select * from sys.partition_range_values
117 --select * from sys.partition_schemes
下面为增加分区部分
代码
Create Procedure [dbo].[Proc_AddHourPartition]
@DbName varchar(50) = 'AAbis', --数据库名
@tbName varchar(50) = 'Part1', -- 表名
@PartitionColumn varchar(50) = 'CallTime', -- 分区字段
@IndexType varchar(50) = 'nonclustered', -- 索引类型(nonclustered|clustered)
--@KeyColumn varchar(100) = 'ID,CallTime', --主键字段
@folderPath varchar(200) = 'Part\', -- 存储位置(每天一个文件24个分区)
@execInterval int = 1, -- 执行间隔（提前几天执行）
@removeInterval int = -2 --归档天数
as
begin
--定义变量
Declare @FilePath varchar(250),
@FGName varchar(50),@FGSql varchar(500), --定义分组
@PFileName varchar(50),@PFileSql varchar(500), --定义分区文件
@PFName varchar(50),@PFSql varchar(500), --定义分区函数
@PSName varchar(50),@PSSql varchar(500), --定义分区架构
@GDSql varchar(500), --定义归档
@DelSql varchar(500), --定义删除
@DelDataSql varchar(500)
--建立新分区执行时间设置
Declare @dtDay datetime, --执行什么时间的数据
@dtDayStr varchar(50), -- 时间串
@dtShort varchar(50), -- 年月日时间字符
@hourNum int=0,
@tempHourStr varchar(10)
--删除分区执行时间设置
Declare @dtDay1 datetime, --执行什么时间的数据
@dtDayStr1 varchar(50), -- 时间串
@dtShort1 varchar(50) -- 年月日时间字符
set @dtDay = dateadd(Day,@execInterval,getdate())
set @dtDayStr = ltrim(Str(YEAR(@dtDay)))+LTRIM(str(month(@dtDay)))+LTRIM(str(day(@dtDay)))
set @dtShort = CONVERT(varchar(100), @dtDay, 111)
set @folderPath=dbo.GetPartitionPath(@dtDay)+@folderPath
set @dtDay1 = dateadd(Day,@removeInterval,getdate())
set @dtDayStr1 = ltrim(Str(YEAR(@dtDay1)))+LTRIM(str(month(@dtDay1)))+LTRIM(str(day(@dtDay1)))
set @dtShort1 = CONVERT(varchar(100), @dtDay1, 111)
--建立分组（每天建立一个分组）
set @FGName = 'FG_'+@tbName +@dtDayStr
set @FGSql = 'ALTER DATABASE AAbis ADD FILEGROUP '+ @FGName
exec(@FGSql)
--创建文件（每天建立一个文件）
set @PFileName = 'PF_'+@tbName +@dtDayStr
set @FilePath = @folderPath+@tbName +@dtDayStr+'.NDF'
set @PFileSql = 'ALTER DATABASE AAbis ADD FILE (NAME = '''+@PFileName+''', FILENAME = '''+@FilePath+''') TO FILEGROUP '+@FGName
exec(@PFileSql)
--创建分区（每天24个分区）
while(@hourNum<24)
begin
--更改分区架构
set @PSName = 'PS_'+@tbName
set @PSSql = 'USE '+@DbName+' ALTER PARTITION SCHEME '+@PSName+' NEXT USED '+@FGName
--select @PSSql
exec(@PSSql)
--更改分区函数
set @PFName = 'PF_'+@tbName+'()'
set @tempHourStr = Ltrim(str(@hourNum))
if(LEN(@tempHourStr)=1)
begin
set @tempHourStr = '0'+ @tempHourStr
end
set @PFSql = 'USE '+@DbName+' ALTER PARTITION FUNCTION '+@PFName+ ' SPLIT RANGE ('''+@dtShort+' '+@tempHourStr+':59:59'')'
if not exists(select * from sys.partition_functions where name=@PFName)
exec(@PFSql)
set @hourNum = @hourNum+1
end
--移除分区
while(@hourNum<24)
begin
--归档和删除数据选其一
--归档分区数据
declare @tb_PartNumber int,@tb_bak_PartNumber int,@dtDeal varchar(50)
set @dtDeal = CONVERT(varchar(100), dateadd(day,@removeInterval,getdate()), 23)--
select top 1 @tb_PartNumber = $PARTITION.PF_MR(Calltime) from MR
where calltime<dateadd(day,@removeInterval,getdate())
if(@tb_PartNumber<>NULL)
begin
set @GDSql = 'ALTER TABLE '+@DbName+'.dbo.'+@tbName+' SWITCH PARTITION '+ltrim(str(@tb_PartNumber))+' TO '+@DbName+'.dbo.'+@tbName+'_History PARTITION '+ltrim(str(@tb_PartNumber))
exec(@GDSql)
end
-- --或删除数据
-- set @DelDataSql = 'delete from '+@DbName+'.dbo.'+@tbName+ ' where '+@PartitionColumn+'<'+@dtShort1
-- exec(@DelDataSql)
-- --删除分区（删除分区后分区号前移）
--set @tempHourStr = Ltrim(str(@hourNum))
--if(LEN(@tempHourStr)=1)
--begin
-- set @tempHourStr = '0'+ @tempHourStr
--end
----检查是否存在这个分区RANGE
--set @DelSql = 'ALTER PARTITION FUNCTION '+@PFName+' MERGE RANGE ('''+@dtShort1+' '+@tempHourStr+':59:59'')'
--BEGIN TRY
-- exec(@DelSql)
--END TRY
-- BEGIN CATCH
-- print 'not exist Partition Range!'
-- END CATCH
set @hourNum =@hourNum+1
end
end
@DbName varchar(50) = 'AAbis', --数据库名
@tbName varchar(50) = 'Part1', -- 表名
@PartitionColumn varchar(50) = 'CallTime', -- 分区字段
@IndexType varchar(50) = 'nonclustered', -- 索引类型(nonclustered|clustered)
--@KeyColumn varchar(100) = 'ID,CallTime', --主键字段
@folderPath varchar(200) = 'Part\', -- 存储位置(每天一个文件24个分区)
@execInterval int = 1, -- 执行间隔（提前几天执行）
@removeInterval int = -2 --归档天数
as
begin
--定义变量
Declare @FilePath varchar(250),
@FGName varchar(50),@FGSql varchar(500), --定义分组
@PFileName varchar(50),@PFileSql varchar(500), --定义分区文件
@PFName varchar(50),@PFSql varchar(500), --定义分区函数
@PSName varchar(50),@PSSql varchar(500), --定义分区架构
@GDSql varchar(500), --定义归档
@DelSql varchar(500), --定义删除
@DelDataSql varchar(500)
--建立新分区执行时间设置
Declare @dtDay datetime, --执行什么时间的数据
@dtDayStr varchar(50), -- 时间串
@dtShort varchar(50), -- 年月日时间字符
@hourNum int=0,
@tempHourStr varchar(10)
--删除分区执行时间设置
Declare @dtDay1 datetime, --执行什么时间的数据
@dtDayStr1 varchar(50), -- 时间串
@dtShort1 varchar(50) -- 年月日时间字符
set @dtDay = dateadd(Day,@execInterval,getdate())
set @dtDayStr = ltrim(Str(YEAR(@dtDay)))+LTRIM(str(month(@dtDay)))+LTRIM(str(day(@dtDay)))
set @dtShort = CONVERT(varchar(100), @dtDay, 111)
set @folderPath=dbo.GetPartitionPath(@dtDay)+@folderPath
set @dtDay1 = dateadd(Day,@removeInterval,getdate())
set @dtDayStr1 = ltrim(Str(YEAR(@dtDay1)))+LTRIM(str(month(@dtDay1)))+LTRIM(str(day(@dtDay1)))
set @dtShort1 = CONVERT(varchar(100), @dtDay1, 111)
--建立分组（每天建立一个分组）
set @FGName = 'FG_'+@tbName +@dtDayStr
set @FGSql = 'ALTER DATABASE AAbis ADD FILEGROUP '+ @FGName
exec(@FGSql)
--创建文件（每天建立一个文件）
set @PFileName = 'PF_'+@tbName +@dtDayStr
set @FilePath = @folderPath+@tbName +@dtDayStr+'.NDF'
set @PFileSql = 'ALTER DATABASE AAbis ADD FILE (NAME = '''+@PFileName+''', FILENAME = '''+@FilePath+''') TO FILEGROUP '+@FGName
exec(@PFileSql)
--创建分区（每天24个分区）
while(@hourNum<24)
begin
--更改分区架构
set @PSName = 'PS_'+@tbName
set @PSSql = 'USE '+@DbName+' ALTER PARTITION SCHEME '+@PSName+' NEXT USED '+@FGName
--select @PSSql
exec(@PSSql)
--更改分区函数
set @PFName = 'PF_'+@tbName+'()'
set @tempHourStr = Ltrim(str(@hourNum))
if(LEN(@tempHourStr)=1)
begin
set @tempHourStr = '0'+ @tempHourStr
end
set @PFSql = 'USE '+@DbName+' ALTER PARTITION FUNCTION '+@PFName+ ' SPLIT RANGE ('''+@dtShort+' '+@tempHourStr+':59:59'')'
if not exists(select * from sys.partition_functions where name=@PFName)
exec(@PFSql)
set @hourNum = @hourNum+1
end
--移除分区
while(@hourNum<24)
begin
--归档和删除数据选其一
--归档分区数据
declare @tb_PartNumber int,@tb_bak_PartNumber int,@dtDeal varchar(50)
set @dtDeal = CONVERT(varchar(100), dateadd(day,@removeInterval,getdate()), 23)--
select top 1 @tb_PartNumber = $PARTITION.PF_MR(Calltime) from MR
where calltime<dateadd(day,@removeInterval,getdate())
if(@tb_PartNumber<>NULL)
begin
set @GDSql = 'ALTER TABLE '+@DbName+'.dbo.'+@tbName+' SWITCH PARTITION '+ltrim(str(@tb_PartNumber))+' TO '+@DbName+'.dbo.'+@tbName+'_History PARTITION '+ltrim(str(@tb_PartNumber))
exec(@GDSql)
end
-- --或删除数据
-- set @DelDataSql = 'delete from '+@DbName+'.dbo.'+@tbName+ ' where '+@PartitionColumn+'<'+@dtShort1
-- exec(@DelDataSql)
-- --删除分区（删除分区后分区号前移）
--set @tempHourStr = Ltrim(str(@hourNum))
--if(LEN(@tempHourStr)=1)
--begin
-- set @tempHourStr = '0'+ @tempHourStr
--end
----检查是否存在这个分区RANGE
--set @DelSql = 'ALTER PARTITION FUNCTION '+@PFName+' MERGE RANGE ('''+@dtShort1+' '+@tempHourStr+':59:59'')'
--BEGIN TRY
-- exec(@DelSql)
--END TRY
-- BEGIN CATCH
-- print 'not exist Partition Range!'
-- END CATCH
set @hourNum =@hourNum+1
end
end
放置分区文件函数：写个简单逻辑，这个根据个人需要编写
代码
Create Function [dbo].[GetPartitionPath](@DealTime datetime) Returns varchar(50) AS
begin
declare @dTime int,@strFolder varchar(50)='\Database\'
set @dTime = day(@DealTime)
return
(
case @dTime
when '1' then 'F:'+ @strFolder
when '2' then 'E:'+ @strFolder
when '3' then 'E:'+ @strFolder
when '4' then 'F:'+ @strFolder
else 'E:'+ @strFolder
end
)
end
begin
declare @dTime int,@strFolder varchar(50)='\Database\'
set @dTime = day(@DealTime)
return
(
case @dTime
when '1' then 'F:'+ @strFolder
when '2' then 'E:'+ @strFolder
when '3' then 'E:'+ @strFolder
when '4' then 'F:'+ @strFolder
else 'E:'+ @strFolder
end
)
end
执行事务：
代码
EXEC [dbo].[Proc_AddHourPartition]
@DbName = N'AAbis',
@tbName = N'Part1',
@PartitionColumn = N'CallTime',
@IndexType = N'nonclustered',
@folderPath = N'Part\',
@execInterval = 5,
@removeInterval = -2
--需要对多少个表分区，写多少个调用即可
@DbName = N'AAbis',
@tbName = N'Part1',
@PartitionColumn = N'CallTime',
@IndexType = N'nonclustered',
@folderPath = N'Part\',
@execInterval = 5,
@removeInterval = -2
--需要对多少个表分区，写多少个调用即可
浙公网安备 33010602011771号