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

小编给大家分享一下数据库中如何自动创建分区函数并按月分区，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！
/*——————–创建数据库的文件组和物理文件————————*/
declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath
varchar(50), 免费主机域名@newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @sche免费主机域名meName 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:SQLDataBaseUserData’+@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:SQLDataBaseUserData’+@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′
看完了这篇文章，相信你对“数据库中如何自动创建分区函数并按月分区”有了一定的了解，如果想了解更多相关知识，欢迎关注云技术行业资讯频道，感谢各位的阅读！

相关推荐: session性能的影响有哪些

这篇文章将为大家详细讲解有关session性能的影响有哪些，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。session处理过程中，不容易发现的性能影响。——后台的暗流涌动，陷入flush dirty。当业务数据量很大，而且变…