MS SQL SERVER 2005数据表分区

1.分区 1)、创建分区函数(partition function)是分区的逻辑设计     例A. CREATE PARTITION FUNCTION Part_Func_Text(INT)               AS RANGE LEFT FRO VALUES(100,200,300)     例B. CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)               AS RANGE LEFT FOR VALUES (                        '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 月 说明:      AS RANGE LEFT  边界值做为前一个分区的最大值      AS RANGE RIGHT 边界值做为后一个分区的最小值 2)、创建分区架构(partition scheme) 是分区的物理设计，指定每个分区存储的文件件，使用多个文件组可以将数据和IO平衡到不同的文件组      例A、CREATE PARTITION SCHEME Part_Schm_Test         AS PARTITION Part_Func_Test TO(Primary,Primary,Primary,Primary)  ---存储在主文件中      例B、步骤1、添加文件组          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]          步骤2、每个物理文件放到了一个单独的文件组          ALTER DATABASE MyDB                ADD FILE                (NAME = N'Teaching200609',FILENAME = N'D:/MyData/MyLu/Teaching200609.ndf',SIZE = 5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)                TO FILEGROUP [Teaching200609]          ALTER DATABASE MyDB                ADD FILE              (NAME = N'Teaching200610',FILENAME = N'D:/MyData/MyLu/Teaching200610.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)                TO FILEGROUP [Teaching200610]          ALTER DATABASE MyDB                ADD FILE              (NAME = N'Teaching200611',FILENAME = N'D:/MyData/MyLu/Teaching200611.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [Teaching200611]          步骤3、          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]) 3)、创建分区表或分区索引：分区表基于分区架构创建。如果在分区表上创建索引，则该索引也是基于同样的分区架构上的分区索引     例1：        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])