SQLSERVER 分区表实战

背景：对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 FUNCTIONFUN_SALES_SYSDATE(DATE) ASRANGE RIGHTFOR VALUES('2013-02-01','2013-03-01','2013-04-01','2013-05-01','2013-06-01','2013-07-01')--创建分区方案CREATE PARTITION SCHEMESCH_SALES_SYSDATE aspARTITION FUN_SALES_SYSDATETO([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])GOALTER 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_SALESGROUP 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])GOALTER 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定期执行做到分区定期自动维护。