Shulou(Shulou.com)05/31 Report--

Editor to share with you how to create partition SP and job in the database. I hope you will get something after reading this article. Let's discuss it together.

Create SP

Click (here) to collapse or open

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: by day

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

-- create filegroup

If exists (select * from sys.filegroups where name=@fileGroupName)

Begin

Print 'filegroup exists, no need to add'

End

Else

Begin

Exec ('ALTER DATABASE' + @ tableName+' ADD FILEGROUP ['+ @ fileGroupName+']')

-- print 'exec' + ('ALTER DATABASE' + @ tableName+' ADD FILEGROUP ['+ @ fileGroupName+']')

Print 'add filegroup'

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 'modify Partition Scheme'

End

Print 'exec' + ('alter partition scheme' + @ schemeName+' next used ['+ @ fileGroupName+']')

Print 'modify Partition Scheme'

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 'modify partition function'

End

End

-- create NDF file

If exists (select * from sys.database_files where [state] = 0 and (name=@ndfName or physical_name=@fullPath))

Begin

Print 'ndf file exists, no need to add'

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 'newly created ndf File'

End

-- / *-the above filegroups and physical files that create the database-* /

End

-Partition function

-- if exists (select * from sys.partition_functions where name = @ partFunName)

-- begin

-- print 'modify here needs to be performed before modifying the partition function'

-- 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 'newly created partition function'

-- end

-Partition scheme

-- if exists (select * from sys.partition_schemes where name = @ schemeName)

-- begin

-- print 'changes here need to be performed before modifying the partition scheme'

-- 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 'newly created Partition Scheme'

two。 Add job

Click (here) to collapse or open

Declare @ date date

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

Print @ date

Exec sp_maintain_partion_fg 'ecodata',@date

After reading this article, I believe you have a certain understanding of "how to create partition SP and job in the database". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.