---1、添加数据文件
use master
go
--Fam200901
alter database RedfDB
add filegroup FGAM200901
go
alter database RedfDB
add file(name='FGAM200901',filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/Fam200901.mdf',
size=20,maxsize=40,filegrowth=10%)to filegroup FGAM200901;
go
--Fam200901
alter database RedfDB
add filegroup FGAM200902
go
alter database RedfDB
add file(name='FGAM200902', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/Fam200902.mdf',
size=20,maxsize=40,filegrowth=10%)to filegroup FGAM200902;
--FGAM200903
alter database RedfDB
add filegroup FGAM200903
go
alter database RedfDB
add file(name='FGAM200903', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200903.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200903;
go
--FGAM200904
alter database RedfDB
add filegroup FGAM200904
go
alter database RedfDB
add file
(name='FGAM200904', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200904.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200904;
go
--FGAM200905
alter database RedfDB
add filegroup FGAM200905
go
alter database RedfDB
add file(name='FGAM200905', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200905.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200905;
go
--FGAM200906
alter database RedfDB
add filegroup FGAM200906
go
alter database RedfDB
add file
(name='FGAM200906', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200906.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200906;
--FGAM200907
alter database RedfDB
add filegroup FGAM200907
go
alter database RedfDB
add file
(name='FGAM200907', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200907.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200907;
--FGAM200908
alter database RedfDB
add filegroup FGAM200908
go
alter database RedfDB
add file
(name='FGAM200908', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200908.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200908;
--FGAM200909
alter database RedfDB
add filegroup FGAM200909
go
alter database RedfDB
add file
(name='FGAM200909', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200909.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200909;
--FGAM200910
alter database RedfDB
add filegroup FGAM200910
go
alter database RedfDB
add file
(name='FGAM200910', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200910.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200910;
--FGAM200911
alter database RedfDB
add filegroup FGAM200911
go
alter database RedfDB
add file
(name='FGAM200911', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200911.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200911;
--FGAM200912
alter database RedfDB
add filegroup FGAM200912
go
alter database RedfDB
add file
(name='FGAM200912', filename='D:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/DATA/FGAM200912.mdf',
size=20, maxsize=40, filegrowth=10%)to filegroup FGAM200912;
--2、创建分区函数
CREATE PARTITION FUNCTION AMReportTimeRangePFN(DateTime)
AS
RANGE LEFT FOR VALUES (
'20090131 23:59:59.997',
'20090228 23:59:59.997',
'20090331 23:59:59.997',
'20090430 23:59:59.997',
'20090531 23:59:59.997',
'20090630 23:59:59.997',
'20090731 23:59:59.997',
'20090831 23:59:59.997',
'20090930 23:59:59.997',
'20091031 23:59:59.997',
'20091130 23:59:59.997',
'20091231 23:59:59.997')
GO
/* 创建分区Scheme */
CREATE PARTITION SCHEME AMReportTimePS
AS
PARTITION AMReportTimeRangePFN
TO ([PRIMARY],
FGAM200901,
FGAM200902,
FGAM200903,
FGAM200904,
FGAM200905,
FGAM200906,
FGAM200907,
FGAM200908,
FGAM200909,
FGAM200910,
FGAM200911,
FGAM200912)
--3、创建分区表
CREATE TABLE [dbo].[TM_ALARMMONITOR](
[REPORTTIME] [datetime] NOT NULL,
[ALARMID] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[TAGID] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL,
[DESCRIBE] [varchar](128) COLLATE Chinese_PRC_CI_AS NULL,
[ALARMTYPE] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL,
[MONITORTYPE] [varchar](16) COLLATE Chinese_PRC_CI_AS NULL,
[SIGN] [varchar](2) COLLATE Chinese_PRC_CI_AS NULL,
[REPORTID] [varchar](12) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_TM_ALARMMONITOR] PRIMARY KEY CLUSTERED
(
[REPORTTIME] ASC,
[ALARMID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON AMReportTimePS([REPORTTIME])
GO