拿出来跟大家分享下,顺便请大家指导下改进的地方。- /*
- 分区表为按月分区
- 根据时间自动添加文件组,文件,修改分区函数,分区方案使其能够在新的一个月来临时自动添加新的分区
- 注:
- 本方法只适用于没有数据的新
- 必须先手动创建好分区表并添加几个文件,文件组
- */
- /*----------------------------------------------分割线----------------------------------------*/
- /*
- 下面脚本在第一次创建分区表时执行
- */
- --分区函数
- CREATE PARTITION FUNCTION IMenuStats_partition(datetime)
- AS RANGE RIGHT FOR VALUES(
- '20120101','20120201','20120301','20120401','20120501','20120601','20120701'
- )
- GO
- --文件组
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120101]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120201]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120301]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120401]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120501]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120601]
- ALTER DATABASE test_shuijingbaobiao ADD FILEGROUP [GROUP20120701]
- GO
- --文件
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120101.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120101]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120201.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120201]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120301.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120301]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120401.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120401]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120501.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120501]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120601.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120601]
- ALTER DATABASE test_shuijingbaobiao
- ADD FILE
- (NAME = N'GROUP2010',FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\GROUP20120701.ndf',
- SIZE=5MB,FILEGROWTH=10% ) TO FILEGROUP [GROUP20120701]
- GO
- --分区方案
- CREATE PARTITION SCHEME [IMenuStatsSCHEME]
- AS
- PARTITION IMenuStats_partition TO
- ([GROUP20120101],[GROUP20120201],[GROUP20120301],[GROUP20120401],[GROUP20120501],[GROUP20120601],[GROUP20120701])
- GO
- /*
- 如果是为已有的普通表改为分区表则需如下脚本
- */
- --先删除主键
- ALTER TABLE IMenuStats DROP constraint PK_IMenuStats_1
- --为表添加主键
- ALTER TABLE IMEnuStats ADD CONSTRAINT PK_IMenustats_1 PRIMARY KEY
- NONCLUSTERED(
- [id] ASC
- )
- GO
- --为表添加聚集索引
- CREATE CLUSTERED INDEX CT_IMenuStats ON IMenuStats([datetime])
- ON IMenuStatsSCHEME(datetime)
- GO
- /*
- 如果直接创建分区表则需如下脚本
- */
- CREATE TABLE [dbo].['表名'](
- [TeachingID] [uniqueidentifier] NOT NULL,
- [TeacherID] [uniqueidentifier] NULL,
- [TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
- [ClassID] [uniqueidentifier] NULL,
- [ClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
- [CourseID] [uniqueidentifier] NULL,
- [CourseName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
- [CourseSequenceID] [uniqueidentifier] NULL,
- [TeachingDate] [datetime] NOT NULL,
- [IsUsingEqt] [bit] NULL,
- [ScoreID] [uniqueidentifier] NULL,
- ......
- ) ON FiveYearDateRangePScheme(TeachingDate)
- --为表添加主键,因为新建分区表添加主键时会为其添加聚集索引
- ALTER TABLE [ObjTeaching]
- ADD CONSTRAINT [ObjTeaching_PK]
- PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])
- GO
- /*-----------------------------------------分割线--------------------------------------*/
- /*
- 以下为JOB中应填入的脚本
- */
- if((select convert(nvarchar(8),month(dateadd(d,+1,getdate())),112))>(select convert(nvarchar(8),month(getdate()),112)))
- begin
- DECLARE @dataBaseName NVARCHAR(50)--数据库名称
- DECLARE @sql NVARCHAR(MAX) --SQL句子
- DECLARE @tableName NVARCHAR(MAX) --表名
- DECLARE @fileName NVARCHAR(MAX) --文件名
- DECLARE @time nvarchar(20) --时间,用于命名
- set @time= convert(nvarchar(8),dateadd(d,+1,getdate()),112)
- set @dataBaseName='test_shuijingbaobiao'
- SET @fileName='FILE'+convert(nvarchar(7),year(getdate()),120)
- SET @tableName = 'GROUP'+ convert(nvarchar(8),dateadd(d,+1,getdate()),112) select @tableName
- set @sql ='ALTER DATABASE ['+@DataBaseName+']
- ADD FILEGROUP ['+@TableName+'];'
- PRINT @sql + CHAR(13)
- exec (@sql)
- end
复制代码 |