本文详细介绍了如何在SQL Server 2005中通过创建分区函数、文件组、分区架构等步骤实现按时间进行数据分区的方法，并提供了一个具体的案例。
SQL SERVER 2005 分区表实际应用例子
posted on 2006年8月24日 23:22 由 吴頔 定义,原理网上讲得多了.在这就不费口舌,记录下创建过程.
一. 最基本,最重要的一步就是创建分区函数.创建分区函数首先要确定分区键--既按照哪字段来进行分区.在这个例子里,我用记录的时间来作为分区键,由于数据量的问题,最终决定每个月的数据放一个单独的分区.
CREATE PARTITION FUNCTION FiveYearDateRangePFN( datetime ) AS RANGE LEFT FOR VALUES ( ' 20060930 23:59:59.997 ' , -- 2006 年 9 月 ' 20061031 23:59:59.997 ' , -- 2006 年 10 月 ' 20061130 23:59:59.997 ' , -- 2006 年 11 月 ' 20061231 23:59:59.997 ' , -- 2006 年 12 月 ' 20070131 23:59:59.997 ' , -- 2007 年 1 月 ' 20070228 23:59:59.997 ' , -- 2007 年 2 月 ' 20070331 23:59:59.997 ' , -- 2007 年 3 月 ' 20070430 23:59:59.997 ' , -- 2007 年 4 月 ' 20070531 23:59:59.997 ' , -- 2007 年 5 月 ' 20070630 23:59:59.997 ' , -- 2007 年 6 月 ' 20070731 23:59:59.997 ' , -- 2007 年 7 月 ' 20070831 23:59:59.997 ' , -- 2007 年 8 月 ' 20070930 23:59:59.997 ' , -- 2007 年 9 月 ' 20071031 23:59:59.997 ' , -- 2007 年 10 月 ' 20071130 23:59:59.997 ' , -- 2007 年 11 月 ' 20071231 23:59:59.997 ' , -- 2007 年 12 月 ....... } GO
二. 上一步是完成一个概念上的分区,接下来要完成一个物理的构建,使得属于不同分区的数据存储到不同的物理文件上去.
a.创建文件组
-- File group for 2006 ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200609 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200610 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200611 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200612 ] -- File group for 2007 ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200701 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200702 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200703 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200704 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200705 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200706 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200707 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200708 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200709 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200710 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200711 ] ALTER DATABASE MyDB ADD FILEGROUP [ Teaching200712 ] ......
b.创建物理文件,在这里,为了方便起见,我把每个物理文件放到了一个单独的文件组里面.
-- Add file for 2006 ALTER DATABASE MyLuDB ADD FILE (NAME = N ' Teaching200609 ' ,FILENAME = N ' D:/MyData/MyLu/Teaching200609.ndf ' ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [ Teaching200609 ] ALTER DATABASE MyLuDB ADD FILE (NAME = N ' Teaching200610 ' ,FILENAME = N ' D:/MyData/MyLu/Teaching200610.ndf ' ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [ Teaching200610 ] ALTER DATABASE MyLuDB ADD FILE (NAME = N ' Teaching200611 ' ,FILENAME = N ' D:/MyData/MyLu/Teaching200611.ndf ' ,SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [ Teaching200611 ] ......
三. 创建完分区函数,接下来就要建立分区架构,用来将概念上的分区和文件组(物理文件)关联起来.
CREATE PARTITION SCHEME [ FiveYearDateRangePScheme ] AS PARTITION FiveYearDateRangePFN TO ( [ Teaching200609 ] , [ Teaching200610 ] , [ Teaching200611 ] , [ Teaching200612 ] , [ Teaching200701 ] , [ Teaching200702 ] , [ Teaching200703 ] , [ Teaching200704 ] , [ Teaching200705 ] , [ Teaching200706 ] , [ Teaching200707 ] , [ Teaching200708 ] , [ Teaching200709 ] , [ Teaching200710 ] , [ Teaching200711 ] , [ Teaching200712 ] , ...... [ PRIMARY ] ) GO
四. 分区表的基础架构到此就完成了,接下来就要建立分区表了.
在建立分区表的时候注意一下分区键的选择就OK了CREATE TABLE [ dbo ] . [ ObjTeaching ] ( [ TeachingID ] [ uniqueidentifier ] NOT NULL , [ TeacherID ] [ uniqueidentifier ] NULL , [ TeacherName ] [ nvarchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL , [ ClassID ] [ uniqueidentifier ] NULL , [ ClassName ] [ nvarchar ] ( 20 ) COLLATE Chinese_PRC_CI_AS NULL , [ CourseID ] [ uniqueidentifier ] NULL , [ CourseName ] [ nvarchar ] ( 20 ) COLLATE Chinese_PRC_CI_AS NULL , [ CourseSequenceID ] [ uniqueidentifier ] NULL , [ TeachingDate ] [ datetime ] NOT NULL , [ IsUsingEqt ] [ bit ] NULL , [ ScoreID ] [ uniqueidentifier ] NULL , ...... ) ON FiveYearDateRangePScheme(TeachingDate) ALTER TABLE [ ObjTeaching ] ADD CONSTRAINT [ ObjTeaching_PK ] PRIMARY KEY CLUSTERED ( [ TeachingID ] , [ TeachingDate ] ) GO
接下来呢,就可以往分区表里面插入数据,SQL SERVER会根据分区键的不同将数据放到相应的分区,我们可以通过如下语句来查看数据存在那个分区中:
select $partition.FiveYearDateRangePFN(teachingdate),teachingdate, * from objteaching a order by a.teachingdate asc
总得说来,SQL SERVER 2005的分区表有了一个非常大的进步,使用起来也比较简单(除了频繁的Copy/Paste脚本,然后在一行行改@_@).至于性能方面,还没时间详细测试,留在以后有空了再说!:)
----2006.8.25 0:03AM 星期五
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
