数据库中如何创建分区的SP和job

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

1. 创建SP

点击(此处)折叠或打开

1. create procedure sp_maintain_partion_fg (
   

2. @tableName varchar(50),
   

3. @inputdate datetime
   

4. )
   

5. as begin
   

6. declare
   

7. @fileGroupName varchar(50),
   

8. @ndfName varchar(50),
   

9. @newNameStr varchar(50),
   

10. @fullPath varchar(50),
    

11. @newDay varchar(50),
    

12. @oldDay datetime,
    

13. @partFunName varchar(50),
    

14. @schemeName varchar(50),
    

15. @sqlstr varchar(1000),
    

16. @sql1 varchar(4000)
    

17. 
    

18. 
    

19. --set @tableName='DYDB'
    

20. set @newDay=CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate), 0), 23 )--CONVERT(varchar(100), @inputdate, 23)--23:按天 114:按时间
    

21. set @oldDay=cast(CONVERT(varchar(10),DATEADD(mm, DATEDIFF(mm,0,@inputdate)-1, 0), 112 ) as datetime)
    

22. set @newNameStr=left(Replace(Replace(@newDay,':','_'),'-','_'),7)
    

23. set @fileGroupName=N'G'+@newNameStr
    

24. set @ndfName=N'F'+@newNameStr+''
    

25. set @fullPath=N'F:\\SQLData\\ecodata\\'+@ndfName+'.ndf'
    

26. set @partFunName=N'pf_Time'
    

27. set @schemeName=N'ps_Time'
    

28. 
    

29. 
    

30. --print @fullPath
    

31. --print @fileGroupName
    

32. --print @ndfName
    

33. 
    

34. 
    

35. 
    

36. 
    

37. --创建文件组
    

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

39. begin
    

40. print '文件组存在,不需添加'
    

41. end
    

42. else
    

43. begin
    

44. exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')
    

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

46. print '新增文件组'
    

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

48. begin
    

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

50. --print 'exec '+('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')
    

51. print '修改分区方案'
    

52. end
    

53. 
    

54. 
    

55. print 'exec '+('alter partition scheme '+@schemeName+'  next used ['+@fileGroupName+']')
    

56. print '修改分区方案'
    

57. 
    

58. 
    

59. if exists(select * from sys.partition_range_values where function_id=(select function_id from
    

60. sys.partition_functions where name =@partFunName) and value=@oldDay)
    

61. begin
    

62. exec('alter partition function  '+@partFunName+'() split range('''+@newDay+''')')
    

63. --print 'exec '+('alter partition function  '+@partFunName+'() split range('''+@newDay+''')')
    

64. print '修改分区函数'
    

65. end
    

66. end
    

67. 
    

68. 
    

69. --创建NDF文件
    

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

71. begin
    

72. print 'ndf文件存在,不需添加'
    

73. end
    

74. else
    

75. begin
    

76. exec('ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')
    

77. print 'ALTER DATABASE '+@tableName+' ADD FILE (NAME ='+@ndfName+',FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']'
    

78. 
    

79. 
    

80. print '新创建ndf文件'
    

81. end
    

82. --
    

83. end
    

84. 
    

85. 
    

86. 
    

87. 
    

88. ----分区函数
    

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

90. --begin
    

91. --print '此处修改需要在修改分区函数之前执行'
    

92. --end
    

93. --else
    

94. --begin
    

95. --exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')')
    

96. ----print 'CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHT FOR VALUES ('''+@newDay+''')'
    

97. --print '新创建分区函数'
    

98. --end
    

99. ----分区方案
    

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

101. --begin
     

102. --print '此处修改需要在修改分区方案之前执行'
     

103. --end
     

104. --else
     

105. --begin
     

106. --exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
     

107. ----print ('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO (''PRIMARY'','''+@fileGroupName+''')')
     

108. --print '新创建分区方案'
     

109. 
     

2. 增加job

点击(此处)折叠或打开

1. declare @date date
   

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

3. print @date
   

4. exec sp_maintain_partion_fg 'ecodata',@date

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