SqlServer2005對(duì)現(xiàn)有數(shù)據(jù)進(jìn)行分區(qū)具體步驟

RegMail是用來(lái)存放注冊(cè)郵件的表,現(xiàn)以創(chuàng)建時(shí)間(CreateTime)字段來(lái)給表進(jìn)行分區(qū),具體步驟如下:

--為分區(qū)創(chuàng)建存儲(chǔ)文件

ALTER DATABASE Test ADD FILEGROUP RegMailFile2007

ALTER DATABASE Test ADD FILEGROUP RegMailFile2008

ALTER DATABASE Test ADD FILEGROUP RegMailFile2009

查看數(shù)據(jù)庫(kù)的文件組能看到如下圖:

--為文件組設(shè)置存儲(chǔ)文件

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

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

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

查看數(shù)據(jù)庫(kù)的存儲(chǔ)文件能看到如下圖:

--創(chuàng)建分區(qū)函數(shù)

CREATE PARTITION FUNCTION pf_RegMail(datetime)

AS

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

創(chuàng)建完了在分區(qū)函數(shù)中可以看到剛創(chuàng)建好的pf_RegMail

如果創(chuàng)建后想對(duì)分區(qū)函數(shù)進(jìn)行修改可以用如下訪求 :

--修改分區(qū)函數(shù)(拆分)

alter PARTITION FUNCTION pf_RegMail()

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

--修改分區(qū)函數(shù)(合并)

ALTER PARTITION FUNCTION pf_RegMail()

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

--創(chuàng)建分區(qū)方案

CREATE PARTITION SCHEME ps_RegMail

AS PARTITION pf_RegMail TO (RegMail2007,RegMail2008,RegMail2009)

如果想去分區(qū)方案進(jìn)行修改

--修改分區(qū)方案

ALTER PARTITION SCHEME ps_RegMail

NEXT USED RegMail2010;

--創(chuàng)建分區(qū)表

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])

--此為關(guān)鍵步驟,將現(xiàn)有數(shù)據(jù)存入上面所建的文件中

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

--如果原來(lái)的表里有主鍵哪就要執(zhí)行下面語(yǔ)句:

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

--查尋數(shù)據(jù)所在文件組

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

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)