共計(jì) 3006 個(gè)字符，預(yù)計(jì)需要花費(fèi) 8 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下數(shù)據(jù)庫中如何創(chuàng)建分區(qū)的 SP 和 job，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
創(chuàng)建 SP
點(diǎn)擊 (此處) 折疊或打開
create 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),
@sql1 varchar(4000)
–set @tableName= DYDB
set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0), 23 )–CONVERT(varchar(100), @inputdate, 23)–23: 按天 114: 按時(shí)間
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 F:\\SQLData\\ecodata\\ +@ndfName+ .ndf
set @partFunName=N pf_Time
set @schemeName=N ps_Time
–print @fullPath
–print @fileGroupName
–print @ndfName
– 創(chuàng)建文件組
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 修改分區(qū)方案
end
print exec +(alter partition scheme +@schemeName+ next used [ +@fileGroupName+] )
print 修改分區(qū)方案
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 修改分區(qū)函數(shù)
end
end
– 創(chuàng)建 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 新創(chuàng)建 ndf 文件
end
–/*——————– 以上創(chuàng)建數(shù)據(jù)庫的文件組和物理文件 ————————*/
end
—- 分區(qū)函數(shù)
–if exists(select * from sys.partition_functions where name =@partFunName)
–begin
–print 此處修改需要在修改分區(qū)函數(shù)之前執(zhí)行
–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 新創(chuàng)建分區(qū)函數(shù)
–end
—- 分區(qū)方案
–if exists(select * from sys.partition_schemes where name =@schemeName)
–begin
–print 此處修改需要在修改分區(qū)方案之前執(zhí)行
–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 新創(chuàng)建分區(qū)方案
2. 增加 job
點(diǎn)擊 (此處) 折疊或打開
declare @date date
set @date= DATEADD(mm,1,getdate())
print @date
exec sp_maintain_partion_fg ecodata ,@date
看完了這篇文章，相信你對(duì)“數(shù)據(jù)庫中如何創(chuàng)建分區(qū)的 SP 和 job”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝各位的閱讀！
