RegMail是用來存放注冊郵件的表,現以創建時間(CreateTime)字段來給表進行分區,具體步驟如下:

--為分區創建存儲文件

ALTER DATABASE Test ADD FILEGROUP RegMailFile2007

ALTER DATABASE Test ADD FILEGROUP RegMailFile2008

ALTER DATABASE Test ADD FILEGROUP RegMailFile2009

查看數據庫的文件組能看到如下圖:

--為文件組設置存儲文件

ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2007', FILENAME = 'E:/Data/RegMailFile2007.NDF') TO FILEGROUP RegMail2007;

ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2008', FILENAME = 'E:/Data/RegMailFile2008.NDF') TO FILEGROUP RegMail2008;

ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2009', FILENAME = 'E:/Data/RegMailFile2009.NDF') TO FILEGROUP RegMail2009;

查看數據庫的存儲文件能看到如下圖:

--創建分區函數

CREATE PARTITION FUNCTION pf_RegMail(datetime)

AS

RANGE RIGHT FOR VALUES ('20070101 00:00:00 ' ,'20080101 00:00:00')

創建完了在分區函數中可以看到剛創建好的pf_RegMail

如果創建后想對分區函數進行修改可以用如下訪求 :

--修改分區函數(拆分)

alter PARTITION FUNCTION pf_RegMail()

split RANGE ('20090101 00:00:00');

--修改分區函數(合并)

ALTER PARTITION FUNCTION pf_RegMail()

MERGE RANGE ('20080101 00:00:00');

--創建分區方案

CREATE PARTITION SCHEME ps_RegMail

AS PARTITION pf_RegMail TO (RegMail2007,RegMail2008,RegMail2009)

如果想去分區方案進行修改

--修改分區方案

ALTER PARTITION SCHEME ps_RegMail

NEXT USED RegMail2010;

--創建分區表

CREATE TABLE [dbo].[PARTITIONERegMail](

[id] [int] IDENTITY(1,1) NOT NULL,

[CreateTime] [datetime] NOT NULL

CONSTRAINT [PK_PARTITIONERegMail] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [ps_RegMail]([CreeateTime])

--此為關鍵步驟,將現有數據存入上面所建的文件中

ALTER TABLE [dbo].[RegMail] WITH NOCHECK ADD
CONSTRAINT [PK_RegMail] PRIMARY KEY CLUSTERED
(
[CreateTime]
) ON [ps_RegMail]([CreateTime])

--如果原來的表里有主鍵哪就要執行下面語句:

alter table RegEmail drop constraint PK_RegEmail--將表的主鍵刪除

--查尋數據所在文件組

SELECT *, $PARTITION.[pf_RegMail](CreateTime) AS PF FROM RegEmail