小编给大家分享一下数据库中如何创建分区的SP和job，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

创建SP

点击(此处)折叠或打开

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:按时间

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

--创建文件组

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 '新创建分区方案'

2. 增加job

点击(此处)折叠或打开

declare @date date

set @date= DATEADD(mm,1,getdate())

print @date

exec sp_maintain_partion_fg 'ecodata',@date

看完了这篇文章，相信你对“数据库中如何创建分区的SP和job”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！