背景：对NEWISS数据库创建分区表T_SALES的SQL。按照日期来进行分区
步骤：
1：创建文件组
2：创建数据文件
3：创建分区函数
4：创建分区方案
5：创建表及聚集索引
6：导入测试数据（此处略），并查询数据分区情况
7：测试交换分区
8：测试合并分区
9：测试拆分分区
--创建7个文件组
ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_1] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_2] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_3] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_4] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_5] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_6] ALTER DATABASE [NEWISS]
ADD FILEGROUP [FG_SALES_SYSDATE_7] --创建7个数据文件
ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_1',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_1.ndf',SIZE = 500MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_1]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_2',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_2.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_2]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_3',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_3.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_3]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_4',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_4.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_4]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_5',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_5.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_5]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_6',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_6.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_6]; ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_SYSDATE_7',FILENAME = N'D:\NEWISS\DBF_SALES_SYSDATE_7.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_SYSDATE_7]; --创建分区函数,边界值使用右分区
CREATE PARTITION FUNCTION
FUN_SALES_SYSDATE(DATE) AS
RANGE RIGHT
FOR VALUES('2013-02-01','2013-03-01','2013-04-01','2013-05-01','2013-06-01','2013-07-01') --创建分区方案
CREATE PARTITION SCHEME
SCH_SALES_SYSDATE AS
PARTITION FUN_SALES_SYSDATE
TO([FG_SALES_SYSDATE_1],[FG_SALES_SYSDATE_2],[FG_SALES_SYSDATE_3],[FG_SALES_SYSDATE_4],[FG_SALES_SYSDATE_5],[FG_SALES_SYSDATE_6],[FG_SALES_SYSDATE_7]) --创建T_SALES表
CREATE TABLE [dbo].[T_SALES](
[sysdate] [date] NOT NULL,
[companyco] [char](2) NOT NULL,
[stco] [char](4) NOT NULL,
[workdate] [date] NOT NULL,
[custype] [char](2) NOT NULL,
[itemco] [char](6) NOT NULL,
[eanco] [varchar](18) NOT NULL,
[divco] [char](2) NULL,
[deptco] [char](2) NULL,
[classco] [char](3) NULL,
[subclassco] [char](2) NULL,
[salesprice] [money] NULL,
[salesprice_nt] [money] NULL,
[salescost] [money] NULL,
[salescost_nt] [money] NULL,
[salescnt] [int] NULL,
[cuscnt] [int] NULL,
[cost] [money] NULL,
[downprice] [money] NULL,
[downcnt] [int] NULL,
[binkbn] [char](1) NULL,
[areaco] [varchar](2) NULL,
[insuser] [varchar](20) NULL,
[insdate] [datetime] NULL,
[upduser] [varchar](20) NULL,
[upddate] [datetime] NULL,
) ON [SCH_SALES_SYSDATE]([SYSDATE])
GO ALTER TABLE [dbo].[T_SALES] ADD CONSTRAINT [PK_SALES_SYSDATE] PRIMARY KEY CLUSTERED
(
[sysdate] ASC,
[itemco] ASC,
[stco] ASC,
[companyco] ASC,
[workdate] ASC,
[custype] ASC
) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [SCH_SALES_SYSDATE]([SYSDATE])
GO --导入测试数据（此处略） --查询数据分区情况
SELECT
$PARTITION.FUN_SALES_SYSDATE(SYSDATE)
,MIN(SYSDATE)
,MAX(SYSDATE)
,COUNT(1)
FROM T_SALES
GROUP BY
$PARTITION.FUN_SALES_SYSDATE(SYSDATE)
ORDER BY
$PARTITION.FUN_SALES_SYSDATE(SYSDATE) --测试交换分区
--先建存档表T_SALES_201301，用来保存2013年2月1日之前的数据
CREATE TABLE [dbo].[T_SALES_201301](
[sysdate] [date] NOT NULL,
[companyco] [char](2) NOT NULL,
[stco] [char](4) NOT NULL,
[workdate] [date] NOT NULL,
[custype] [char](2) NOT NULL,
[itemco] [char](6) NOT NULL,
[eanco] [varchar](18) NOT NULL,
[divco] [char](2) NULL,
[deptco] [char](2) NULL,
[classco] [char](3) NULL,
[subclassco] [char](2) NULL,
[salesprice] [money] NULL,
[salesprice_nt] [money] NULL,
[salescost] [money] NULL,
[salescost_nt] [money] NULL,
[salescnt] [int] NULL,
[cuscnt] [int] NULL,
[cost] [money] NULL,
[downprice] [money] NULL,
[downcnt] [int] NULL,
[binkbn] [char](1) NULL,
[areaco] [varchar](2) NULL,
[insuser] [varchar](20) NULL,
[insdate] [datetime] NULL,
[upduser] [varchar](20) NULL,
[upddate] [datetime] NULL,
) ON [SCH_SALES_SYSDATE]([SYSDATE])
GO ALTER TABLE [dbo].[T_SALES_201301] ADD CONSTRAINT [PK_SALES_201301_SYSDATE] PRIMARY KEY CLUSTERED
(
[sysdate] ASC,
[itemco] ASC,
[stco] ASC,
[companyco] ASC,
[workdate] ASC,
[custype] ASC
) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [SCH_SALES_SYSDATE]([SYSDATE])
GO --交换分区
ALTER TABLE [dbo].[T_SALES] SWITCH PARTITION 1 TO [dbo].[T_SALES_201301] PARTITION 1 --合并分区（将2013年2月与3月的数据合并）
ALTER PARTITION FUNCTION FUN_SALES_SYSDATE() MERGE RANGE ('2013-03-01') --拆分分区（将2013年8月1日以后的数据放到新分区）
--1：先指定分区方案下一个分区使用哪个文件组（如果需使用新文件组则需要再创建一个文件组，本例中使用刚交换出去的文件组FG_SALES_SYSDATE_1）
ALTER PARTITION SCHEME SCH_SALES_SYSDATE NEXT USED FG_SALES_SYSDATE_1
--2：拆分（指定拆分分界点2013-08-01）
ALTER PARTITION FUNCTION FUN_SALES_SYSDATE() SPLIT RANGE ('2013-08-01') --使用存储过程适当进行sql拼接进行拆分分区/交换分区存档数据的操作，建立job定期执行做到分区定期自动维护。