SqlServer分区表语法

@H_674_6@--@H_674_6@创建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]

@H_674_6@--@H_674_6@创建7个数据文件
ALTER DATABASE [NEWISS]
ADD FILE
(NAME = N'DBF_SALES_sysdate_1',FILename = N'D:NEWISSDBF_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:NEWISSDBF_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:NEWISSDBF_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:NEWISSDBF_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:NEWISSDBF_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:NEWISSDBF_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:NEWISSDBF_SALES_sysdate_7.ndf',SIZE = 500MB , FILEGROWTH = 10MB )
TO FILEGROUP [FG_SALES_sysdate_7];

@H_674_6@--@H_674_6@创建分区函数,边界值使用右分区
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')

@H_674_6@--@H_674_6@创建分区方案
CREATE PARTITION scheR_267_11845@E
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])

@H_674_6@--@H_674_6@创建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] [@H_467_14@money] NULL,
    [SALEsprice_nt] [@H_467_14@money] NULL,
    [SALEscost] [@H_467_14@money] NULL,
    [SALEscost_nt] [@H_467_14@money] NULL,
    [SALEscnt] [int] NULL,
    [cuscnt] [int] NULL,
    [cost] [@H_467_14@money] NULL,
    [downprice] [@H_467_14@money] NULL,
    [downcnt] [int] NULL,
    [binkbn] [char](1) NULL,
    [areaco] [varchar](2) NULL,
    [insuser] [varchar](20) NULL,
    [insdate] [datetiR_267_11845@e] NULL,
    [upduser] [varchar](20) NULL,
    [upddate] [datetiR_267_11845@e] 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

@H_674_6@--@H_674_6@导入测试数据（此处略）

@H_674_6@--@H_674_6@查询数据分区情况
SELECT
 $PARTITION.FUN_SALES_sysdate(sysdate)
,@H_318_1242@mIN(sysdate)
,@H_318_1242@mAX(sysdate)
,COUNT(1)
FROM T_SALES
GROUP BY
 $PARTITION.FUN_SALES_sysdate(sysdate)
ORDER BY
 $PARTITION.FUN_SALES_sysdate(sysdate)

@H_674_6@--@H_674_6@测试交换分区@H_674_6@
--@H_674_6@先建存档表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] [@H_467_14@money] NULL,
    [SALEsprice_nt] [@H_467_14@money] NULL,
    [SALEscost] [@H_467_14@money] NULL,
    [SALEscost_nt] [@H_467_14@money] NULL,
    [SALEscnt] [int] NULL,
    [cuscnt] [int] NULL,
    [cost] [@H_467_14@money] NULL,
    [downprice] [@H_467_14@money] NULL,
    [downcnt] [int] NULL,
    [binkbn] [char](1) NULL,
    [areaco] [varchar](2) NULL,
    [insuser] [varchar](20) NULL,
    [insdate] [datetiR_267_11845@e] NULL,
    [upduser] [varchar](20) NULL,
    [upddate] [datetiR_267_11845@e] 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

@H_674_6@--@H_674_6@交换分区
ALTER TABLE [dbo].[T_SALES] SWITCH PARTITION 1 TO [dbo].[T_SALES_201301] PARTITION 1

@H_674_6@--@H_674_6@合并分区（将2013年2月与3月的数据合并）
ALTER PARTITION FUNCTION FUN_SALES_sysdate() MERGE RANGE ('2013-03-01')

@H_674_6@--@H_674_6@拆分分区（将2013年8月1日以后的数据放到新分区）@H_674_6@
--@H_674_6@1：先指定分区方案下一个分区使用哪个文件组（如果需使用新文件组则需要再创建一个文件组，本例中使用刚交换出去的文件组FG_SALES_sysdate_1）
ALTER PARTITION scheR_267_11845@E SCH_SALES_sysdate NEXT USED FG_SALES_sysdate_1
@H_674_6@--@H_674_6@2：拆分（指定拆分分界点2013-08-01）
ALTER PARTITION FUNCTION FUN_SALES_sysdate() SPLIT RANGE ('2013-08-01')

@H_674_6@--@H_674_6@使用存储过程适当进行sql拼接进行拆分分区/交换分区存档数据的操作，建立job定期执行做到分区定期自动维护。