拿出来跟大家分享下，顺便请大家指导下改进的地方。

1. /*
   
2. 分区表为按月分区
   
3. 根据时间自动添加文件组，文件，修改分区函数，分区方案使其能够在新的一个月来临时自动添加新的分区
   
4. 
   
5. 注：
   
6. 本方法只适用于没有数据的新
   
7. 必须先手动创建好分区表并添加几个文件，文件组
   
8. */
   
9. /*----------------------------------------------分割线----------------------------------------*/
   
10. 
    
11. /*
    
12. 下面脚本在第一次创建分区表时执行
    
13. */
    
14. --分区函数
    
15. CREATE PARTITION FUNCTION IMenuStats_partition(datetime)
    
16. AS RANGE RIGHT FOR VALUES(
    
17. '20120101','20120201','20120301','20120401','20120501','20120601','20120701'
    
18. )
    
19. GO
    
20. 
    
21. --文件组
    
22. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120101]
    
23. ALTER DATABASE test_shuijingbaobiao        ADD FILEGROUP [GROUP20120201]
    
24. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120301]
    
25. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120401]
    
26. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120501]
    
27. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120601]
    
28. ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120701]
    
29. GO
    
30. 
    
31. --文件
    
32. ALTER DATABASE test_shuijingbaobiao
    
33. ADD FILE
    
34. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120101.ndf',
    
35. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120101]
    
36. 
    
37. ALTER DATABASE test_shuijingbaobiao
    
38. ADD FILE
    
39. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120201.ndf',
    
40. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120201]
    
41. 
    
42. ALTER DATABASE test_shuijingbaobiao
    
43. ADD FILE
    
44. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120301.ndf',
    
45. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120301]
    
46. ALTER DATABASE test_shuijingbaobiao
    
47. ADD FILE
    
48. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120401.ndf',
    
49. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120401]
    
50. 
    
51. ALTER DATABASE test_shuijingbaobiao
    
52. ADD FILE
    
53. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120501.ndf',
    
54. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120501]
    
55. 
    
56. ALTER DATABASE test_shuijingbaobiao
    
57. ADD FILE
    
58. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120601.ndf',
    
59. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120601]
    
60. 
    
61. ALTER DATABASE test_shuijingbaobiao
    
62. ADD FILE
    
63. (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120701.ndf',
    
64. SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120701]
    
65. GO
    
66. 
    
67. --分区方案
    
68. CREATE PARTITION SCHEME [IMenuStatsSCHEME]
    
69. AS
    
70. PARTITION IMenuStats_partition TO
    
71. ([GROUP20120101],[GROUP20120201],[GROUP20120301],[GROUP20120401],[GROUP20120501],[GROUP20120601],[GROUP20120701])
    
72. GO
    
73. 
    
74. /*
    
75. 如果是为已有的普通表改为分区表则需如下脚本
    
76. */
    
77. --先删除主键
    
78. ALTER TABLE IMenuStats DROP constraint PK_IMenuStats_1
    
79. --为表添加主键
    
80. ALTER TABLE IMEnuStats ADD CONSTRAINT PK_IMenustats_1 PRIMARY KEY
    
81. NONCLUSTERED(
    
82.         [id] ASC
    
83. )
    
84. GO
    
85. --为表添加聚集索引
    
86. CREATE CLUSTERED INDEX CT_IMenuStats ON IMenuStats([datetime])
    
87. ON IMenuStatsSCHEME(datetime)
    
88. GO
    
89. 
    
90. /*
    
91. 如果直接创建分区表则需如下脚本
    
92. */
    
93. CREATE TABLE [dbo].['表名'](  
    
94.   [TeachingID] [uniqueidentifier] NOT NULL,  
    
95.   [TeacherID] [uniqueidentifier] NULL,  
    
96.   [TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,  
    
97.   [ClassID] [uniqueidentifier] NULL,  
    
98.   [ClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,  
    
99.   [CourseID] [uniqueidentifier] NULL,  
    
100.   [CourseName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,  
     
101.   [CourseSequenceID] [uniqueidentifier] NULL,  
     
102.   [TeachingDate] [datetime] NOT NULL,  
     
103.   [IsUsingEqt] [bit] NULL,  
     
104.   [ScoreID] [uniqueidentifier] NULL,  
     
105.   ......  
     
106. ) ON FiveYearDateRangePScheme(TeachingDate)  
     
107. --为表添加主键，因为新建分区表添加主键时会为其添加聚集索引
     
108. ALTER TABLE [ObjTeaching]  
     
109. ADD CONSTRAINT [ObjTeaching_PK]  
     
110. PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])  
     
111. GO
     
112. 
     
113. /*-----------------------------------------分割线--------------------------------------*/
     
114. 
     
115. /*
     
116. 以下为JOB中应填入的脚本
     
117. */
     
118. if((select convert(nvarchar(8),month(dateadd(d,+1,getdate())),112))>(select convert(nvarchar(8),month(getdate()),112)))
     
119. begin
     
120. 
     
121. DECLARE @dataBaseName NVARCHAR(50)--数据库名称
     
122. DECLARE @sql NVARCHAR(MAX) --SQL句子
     
123. DECLARE @tableName NVARCHAR(MAX) --表名
     
124. DECLARE @fileName NVARCHAR(MAX) --文件名
     
125. DECLARE @time nvarchar(20) --时间，用于命名
     
126. 
     
127. set @time= convert(nvarchar(8),dateadd(d,+1,getdate()),112)
     
128. 
     
129. set @dataBaseName='test_shuijingbaobiao'
     
130. SET @fileName='FILE'+convert(nvarchar(7),year(getdate()),120)
     
131. SET @tableName = 'GROUP'+ convert(nvarchar(8),dateadd(d,+1,getdate()),112) select @tableName
     
132. set @sql ='ALTER DATABASE ['+@DataBaseName+']
     
133. ADD FILEGROUP ['+@TableName+'];'
     
134. PRINT @sql + CHAR(13)
     
135. exec (@sql)
     
136. 
     
137. end

复制代码

我勒个去，发完了才注意到代码木有高亮显示啊，这估计看起来会很蛋疼。。。。