分享一个以前自己写的关于MSSQLSERVER分区表自动分区的实例 - 黑马程序员技术交流社区

/*
分区表为按月分区
根据时间自动添加文件组，文件，修改分区函数，分区方案使其能够在新的一个月来临时自动添加新的分区
注：
本方法只适用于没有数据的新
必须先手动创建好分区表并添加几个文件，文件组
*/
/*----------------------------------------------分割线----------------------------------------*/
/*
下面脚本在第一次创建分区表时执行
*/
--分区函数
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

复制代码