數據庫中如何自動創建分區函數并按月分區

小編給大家分享一下數據庫中如何自動創建分區函數并按月分區，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

/*--------------------創建數據庫的文件組和物理文件------------------------*/
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

--exec sp_maintain_partion_fg 'XXXX','2013-03-20'

看完了這篇文章，相信你對“數據庫中如何自動創建分區函數并按月分區”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！