数据库中如何自动创建分区函数并按月分区

66次阅读

共计 5900 个字符，预计需要花费 15 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下数据库中如何自动创建分区函数并按月分区，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

/*——————– 创建数据库的文件组和物理文件 ————————*/
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50),
@sqlstr varchar(1000)

set @tableName= DYDB
set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,getdate()), 0), 23 )–CONVERT(varchar(100), GETDATE(), 23)–23: 按天 114: 按时间
set @oldDay=cast(CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0), 112 ) as datetime)
set @newNameStr=left(Replace(Replace(@newDay, : , _), – , _ ),7)
set @fileGroupName=N G +@newNameStr
set @ndfName=N F +@newNameStr+
set @fullPath=N E:\\SQLDataBase\\UserData\\ +@ndfName+ .ndf
set @partFunName=N pf_Time
set @schemeName=N ps_Time

print @fullPath
print @fileGroupName
print @ndfName

– 创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print 文件组存在, 不需添加
end
else
begin
–exec(ALTER DATABASE +@tableName+ ADD FILEGROUP [ +@fileGroupName+] )
print exec +(ALTER DATABASE +@tableName+ ADD FILEGROUP [ +@fileGroupName+] )
print 新增文件组
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
–exec(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print exec +(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print 修改分区方案
end

print exec +(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print 修改分区方案

if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
–exec(alter partition function +@partFunName+ () split range(+@newDay+) )
print exec +(alter partition function +@partFunName+ () split range(+@newDay+) )
print 修改分区函数
end
end

– 创建 NDF 文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print ndf 文件存在, 不需添加
end
else
begin
–exec(ALTER DATABASE +@tableName+ ADD FILE (NAME = +@ndfName+ ,FILENAME = +@fullPath+)TO FILEGROUP [+@fileGroupName+] )
print ALTER DATABASE +@tableName+ ADD FILE (NAME = +@ndfName+ ,FILENAME = +@fullPath+)TO FILEGROUP [+@fileGroupName+]

print 新创建 ndf 文件
end
–/*——————– 以上创建数据库的文件组和物理文件 ————————*/

– 分区函数
if exists(select * from sys.partition_functions where name =@partFunName)
begin
print 此处修改需要在修改分区函数之前执行
end
else
begin
–exec(CREATE PARTITION FUNCTION +@partFunName+ (DateTime)AS RANGE RIGHT FOR VALUES (+@newDay+) )
print CREATE PARTITION FUNCTION +@partFunName+ (DateTime)AS RANGE RIGHT FOR VALUES (+@newDay+)
print 新创建分区函数
end
– 分区方案
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
print 此处修改需要在修改分区方案之前执行
end
else
begin
–exec(CREATE PARTITION SCHEME +@schemeName+ AS PARTITION +@partFunName+ TO ( PRIMARY , +@fileGroupName+) )
print (CREATE PARTITION SCHEME +@schemeName+ AS PARTITION +@partFunName+ TO ( PRIMARY , +@fileGroupName+) )
print 新创建分区方案
end
–print ————— 以下是变量定义值显示 ———————
–print 当前数据库: +@tableName
–print 当前日期: +@newDay+ (用作随机生成的各种名称和分区界限)
–print 合法命名方式: +@newNameStr
–print 文件组名称: +@fileGroupName
–print ndf 物理文件名称: +@ndfName
–print 物理文件完整路径: +@fullPath
–print 分区函数: +@partFunName
–print 分区方案: +@schemeName
–/*

写成 SP

–select @@servername

alter procedure sp_maintain_partion_fg (
@tableName varchar(50),
@inputdate datetime
)
as begin
declare
@fileGroupName varchar(50),
@ndfName varchar(50),
@newNameStr varchar(50),
@fullPath varchar(50),
@newDay varchar(50),
@oldDay datetime,
@partFunName varchar(50),
@schemeName varchar(50),
@sqlstr varchar(1000)

–set @tableName= DYDB
set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0), 23 )–CONVERT(varchar(100), @inputdate, 23)–23: 按天 114: 按时间
set @oldDay=cast(CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate)-1, 0), 112 ) as datetime)
set @newNameStr=left(Replace(Replace(@newDay, : , _), – , _ ),7)
set @fileGroupName=N G +@newNameStr
set @ndfName=N F +@newNameStr+
set @fullPath=N E:\\SQLDataBase\\UserData\\ +@ndfName+ .ndf
set @partFunName=N pf_Time
set @schemeName=N ps_Time

print @fullPath
print @fileGroupName
print @ndfName

– 创建文件组
if exists(select * from sys.filegroups where name=@fileGroupName)
begin
print 文件组存在, 不需添加
end
else
begin
–exec(ALTER DATABASE +@tableName+ ADD FILEGROUP [ +@fileGroupName+] )
print exec +(ALTER DATABASE +@tableName+ ADD FILEGROUP [ +@fileGroupName+] )
print 新增文件组
if exists(select * from sys.partition_schemes where name =@schemeName)
begin
–exec(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print exec +(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print 修改分区方案
end

print exec +(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print 修改分区方案

if exists(select * from sys.partition_range_values where function_id=(select function_id from
sys.partition_functions where name =@partFunName) and value=@oldDay)
begin
–exec(alter partition function +@partFunName+ () split range(+@newDay+) )
print exec +(alter partition function +@partFunName+ () split range(+@newDay+) )
print 修改分区函数
end
end

– 创建 NDF 文件
if exists(select * from sys.database_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))
begin
print ndf 文件存在, 不需添加
end
else
begin
–exec(ALTER DATABASE +@tableName+ ADD FILE (NAME = +@ndfName+ ,FILENAME = +@fullPath+)TO FILEGROUP [+@fileGroupName+] )
print ALTER DATABASE +@tableName+ ADD FILE (NAME = +@ndfName+ ,FILENAME = +@fullPath+)TO FILEGROUP [+@fileGroupName+]

print 新创建 ndf 文件
end
–/*——————– 以上创建数据库的文件组和物理文件 ————————*/
end

—- 分区函数
–if exists(select * from sys.partition_functions where name =@partFunName)
–begin
–print 此处修改需要在修改分区函数之前执行
–end
–else
–begin
—-exec(CREATE PARTITION FUNCTION +@partFunName+ (DateTime)AS RANGE RIGHT FOR VALUES (+@newDay+) )
–print CREATE PARTITION FUNCTION +@partFunName+ (DateTime)AS RANGE RIGHT FOR VALUES (+@newDay+)
–print 新创建分区函数
–end
—- 分区方案
–if exists(select * from sys.partition_schemes where name =@schemeName)
–begin
–print 此处修改需要在修改分区方案之前执行
–end
–else
–begin
—-exec(CREATE PARTITION SCHEME +@schemeName+ AS PARTITION +@partFunName+ TO ( PRIMARY , +@fileGroupName+) )
–print (CREATE PARTITION SCHEME +@schemeName+ AS PARTITION +@partFunName+ TO ( PRIMARY , +@fileGroupName+) )
–print 新创建分区方案
–end