Stone 2008-8-12 09:38
SqlServer2005对现有数据进行分区具体步骤
[font=SimSun][size=10pt]RegMail是用来存放注册邮件的表,现以创建时间(CreateTime)字段来给表进行分区,具体步骤如下:[/size][/font]
[font=SimSun][size=10pt]--为分区创建存储文件
[/size][/font]
[font=SimSun][size=9pt]ALTER DATABASE Test ADD FILEGROUP RegMailFile2007[/size][/font]
[font=SimSun][size=9pt]ALTER DATABASE Test ADD FILEGROUP RegMailFile2008[/size][/font]
[font=SimSun][size=9pt]ALTER DATABASE Test ADD FILEGROUP RegMailFile2009[/size][/font]
[font=SimSun][size=10pt]查看数据库的文件组能看到如下图:[/size][/font]
[font=SimSun][size=10pt][img]http://www.chinaz.com/upimg/allimg/080624/1018200.jpg[/img][/size][/font]
[font=SimSun][size=10pt]--为文件组设置存储文件[/size][/font]
[font=SimSun][size=10pt]ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2007', FILENAME = 'E:\Data\RegMailFile2007.NDF') TO FILEGROUP RegMail2007;[/size][/font]
[font=SimSun][size=10pt]ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2008', FILENAME = 'E:\Data\RegMailFile2008.NDF') TO FILEGROUP RegMail2008;[/size][/font]
[font=SimSun][size=10pt]ALTER DATABASE Test ADD FILE (NAME = 'RegMailFile2009', FILENAME = 'E:\Data\RegMailFile2009.NDF') TO FILEGROUP RegMail2009;[/size][/font]
[font=SimSun][size=10pt]查看数据库的存储文件能看到如下图:[/size][/font]
[img]http://www.chinaz.com/upimg/allimg/080624/1018201.jpg[/img]
[font=SimSun][size=10pt]--创建分区函数[/size][/font]
[font=SimSun][size=10pt]CREATE PARTITION FUNCTION pf_RegMail(datetime)[/size][/font]
[font=SimSun][size=10pt]AS[/size][/font]
[font=SimSun][size=10pt]RANGE RIGHT FOR VALUES ( ' 20070101 00:00:00 ' ,'20080101 00:00:00')[/size][/font]
[font=SimSun][size=10pt]创建完了在分区函数中可以看到刚创建好的pf_RegMail
[/size][/font]
[font=SimSun][size=10pt][img]http://www.chinaz.com/upimg/allimg/080624/1018202.jpg[/img]
[/size][/font]
[font=SimSun][size=10pt]如果创建后想对分区函数进行修改可以用如下访求 :[/size][/font]
[font=SimSun][size=10pt]--修改分区函数(拆分)[/size][/font]
[font=SimSun][size=10pt]alter PARTITION FUNCTION pf_RegMail()[/size][/font]
[font=SimSun][size=10pt]split RANGE ('20090101 00:00:00');[/size][/font]
[font=SimSun][size=10pt]--修改分区函数(合并)[/size][/font]
[font=SimSun][size=10pt]ALTER PARTITION FUNCTION pf_RegMail()[/size][/font]
[font=SimSun][size=10pt]MERGE RANGE ('20080101 00:00:00');[/size][/font]
[font=SimSun][size=10pt]--创建分区方案[/size][/font]
[font=SimSun][size=10pt]CREATE PARTITION SCHEME ps_RegMail[/size][/font]
[font=SimSun][size=10pt]AS PARTITION pf_RegMail TO (RegMail2007,RegMail2008,RegMail2009)[/size][/font]
[font=SimSun][size=10pt]如果想去分区方案进行修改
[/size][/font]
[font=SimSun][size=10pt]--修改分区方案[/size][/font]
[font=SimSun][size=10pt]ALTER PARTITION SCHEME ps_RegMail[/size][/font]
[font=SimSun][size=10pt]NEXT USED RegMail2010;[/size][/font]
[font=SimSun][size=10pt]--创建分区表[/size][/font]
[font=SimSun][size=10pt]CREATE TABLE [dbo].[PARTITIONERegMail]([/size][/font]
[font=SimSun][size=10pt][id] [int] IDENTITY(1,1) NOT NULL,[/size][/font]
[font=SimSun][size=10pt][CreateTime] [datetime] NOT NULL[/size][/font]
[font=SimSun][size=10pt] CONSTRAINT [PK_PARTITIONERegMail] PRIMARY KEY NONCLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [ps_RegMail]([CreeateTime])[/size][/font]
[font=SimSun][size=10pt]--此为关键步骤,将现有数据存入上面所建的文件中
[/size][/font]
[font=SimSun][size=10pt]ALTER TABLE [dbo].[RegMail] WITH NOCHECK ADD
CONSTRAINT [PK_RegMail] PRIMARY KEY CLUSTERED
(
[CreateTime]
) ON [ps_RegMail]([CreateTime])[/size][/font]
[font=SimSun][size=10pt]
--如果原来的表里有主键哪就要执行下面语句:
[/size][/font]
[font=SimSun][size=10pt][/size][/font]
[font=SimSun][size=10pt]alter table RegEmail drop constraint PK_RegEmail--将表的主键删除[/size][/font]
[font=SimSun][size=10pt]--查寻数据所在文件组[/size][/font]
[font=SimSun][size=10pt]SELECT *, $PARTITION.[pf_RegMail](CreateTime) AS PF FROM RegEmail[/size][/font]