SQL Server2005 表分区

---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