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

Editor to share with you how to automatically create partition functions in the database and partition on a monthly basis. I hope you will get something after reading this article. Let's discuss it together.

/ *-create filegroups and physical files for the database-

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

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

-- 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-* /

-- 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'

End

-- print'--the following is the variable definition value display -'

-- print 'current database:' + @ tableName

-- print 'current date:' + @ newDay+' (used as randomly generated names and partition boundaries)'

-- print 'legal naming method:' + @ newNameStr

-- print 'filegroup name:' + @ fileGroupName

-- print 'ndf physical file name:' + @ ndfName

-- print 'full path to the physical file:' + @ fullPath

-- print 'partition function:' + @ partFunName

-- print 'partition scheme:' + @ schemeName

-/ *

Write as 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: 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'E:\\ SQLDataBase\\ UserData\'+ @ 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'

-- end

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

After reading this article, I believe you have a certain understanding of "how to automatically create partition functions in the database and partition by month". If you want to know more about it, 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.