背景：對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定期執行做到分區定期自動維護。