[转]SQLServer2005分区表实践——建立分区表（partitiontable）

【鹏城万里】 发表于 www.sqlstudy.com

SQL Server 2005 分区表实践——建立分区表&＃xff08;partition table&＃xff09;

问题&＃xff1a;有一个订单表 Orders&＃xff0c;要转换成分区表&＃xff0c;以订单日期 OrderDate 为分区列&＃xff0c; 目前含有订单日期为 1996-07-04 ~ 1998-05-06 的数据。可以在 SQL Server 2000 Northwind 数据库中找到 Orders 表&＃xff0c;下面是简化了的表结构&＃xff1a;

create table dbo.Orders
(OrderID int not null,CustomerID varchar(10) not null,EmployeeID int not null,OrderDate datetime not null,constraint PK_Orders primary key noclustered (OrderID, CustomerID)
)
gocreate clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go


1. 创建分区函数&＃xff08;partition function&＃xff09;

在创建分区函数的时候&＃xff0c;我一般这样命名分区函数&＃xff1a;&＃39;PF&＃39; &＃43; Table Name &＃43; 分区字段名 &＃43; &＃39;Range&＃39;&＃xff0c; 例如&＃xff1a;&＃39;PF_Orders_OrderDateRange&＃39;&＃xff0c;如果字段名较长的话&＃xff0c;则可以省略去&＃xff0c;可以这样命名&＃xff1a; &＃39;PF_Orders_DateRange&＃39;

create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
&＃39;1997-01-01&＃39;,
&＃39;1998-01-01&＃39;,
&＃39;1999-01-01&＃39;
)
go


分区函数 &＃39;PF_Orders_OrderDateRange&＃39; 有 3 个边界值&＃xff08;&＃39;1997-01-01&＃39;, &＃39;1998-01-01&＃39;, &＃39;1999-01-01&＃39;&＃xff09;&＃xff0c; 这三个边界值&＃xff08;boundary value&＃xff09;组成了 4 个分区&＃xff08;partition&＃xff09;&＃xff0c;并且 “range right” 表明边界值属于 右边的分区。下面使用存储过程 dbo.sp_show_partition_range 来查看分区范围&＃xff1a;

exec dbo.sp_show_partition_range &＃64;partition_function &＃61; &＃39;PF_Orders_OrderDateRange&＃39;


partition_function partition minval value maxval
------------------------- ----------- ------------------------- -------- ------------------------
PF_Orders_OrderDateRange 1 NULL <&＃61; val <1997-01-01 00:00:00.000
PF_Orders_OrderDateRange 2 1997-01-01 00:00:00.000 <&＃61; val <1998-01-01 00:00:00.000
PF_Orders_OrderDateRange 3 1998-01-01 00:00:00.000 <&＃61; val <1999-01-01 00:00:00.000
PF_Orders_OrderDateRange 4 1999-01-01 00:00:00.000 <&＃61; val

每个分区的最大值和最小值&＃xff0c;一清二楚。获得 dbo.sp_show_partition_range 代码。

2. 创建分区方案&＃xff08;partition scheme&＃xff09;

分区方案定义了&＃xff0c;分区表或者分区索引的每个分区的数据存放在哪个文件组上。试想&＃xff0c; 如果没有分区方案的话&＃xff0c;如何实现把分区表中不同的分区存放在不同的文件组上&＃xff1f; 我们可以看下创建普通数据表的语法&＃xff1a;

create table [table name] ... on [filegroup]


显然&＃xff0c;普通表整个表的数据&＃xff0c;只能存放在同一个文件组上。为了实现分区机制&＃xff0c;才 引入了分区方案这个概念。每个分区表只属于一个方案&＃xff08;scheme&＃xff09;&＃xff0c; 因此分区方案可以这样命名&＃xff1a;&＃39;PS&＃39; &＃43; Table Name

create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go


分区方案 PS_Orders 规定了&＃xff0c;把分区表的 4 个分区分别存放在主文件组 primary 上。 分区方案中指定的文件组数目&＃xff0c;不能少于分区函数中划定的分区数目&＃xff1b;但可以多于 分区函数中划定的分区数目。多出的第 1 个文件组用来指定当分区表增加分区时&＃xff0c; 下一个分区所使用的文件组&＃xff1b;多出的其他文件组将被忽略。

下面的例子&＃xff0c;分区方案指定了 5 个文件组&＃xff08;多出了 1 个文件组&＃xff09;。

drop partition scheme PS_Orders
gocreate partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary])
go


Partition scheme &＃39;PS_Orders&＃39; has been created successfully.
&＃39;PRIMARY&＃39; is marked as the next used filegroup in partition scheme &＃39;PS_Orders&＃39;.


下面的例子&＃xff0c;分区方案指定了 7 个文件组&＃xff08;多出了 3 个文件组&＃xff09;。

drop partition scheme PS_Orders
gocreate partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary],[primary], [primary],[primary],[primary],[primary])
go


Partition scheme &＃39;PS_Orders&＃39; has been created successfully.
&＃39;PRIMARY&＃39; is marked as the next used filegroup in partition scheme &＃39;PS_Orders&＃39;.
2 filegroup specified after the next used filegroup are ignored.


如果分区表所有的分区都将分配在同一个文件组 [primary] 上&＃xff0c;那么可以使用下面更简洁的方法&＃xff1a;

drop partition scheme PS_Orders
gocreate partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
all to ([primary])
go


3. 创建分区表&＃xff08;partition table&＃xff09;

创建了分区函数和分区方案&＃xff0c;准备工作做完了&＃xff0c;现在终于可以开始创建分区表了。 创建分区表和普通表的语法大致相同&＃xff0c;不同之处&＃xff1a;普通表需要指定所存放的文件组&＃xff0c; 分区表需要指定分区方案。

create table dbo.Orders
(OrderID int not null,CustomerID varchar(10) not null,EmployeeID int not null,OrderDate datetime not null
)
on PS_Orders(OrderDate)
go


根据订单表 Orders 查询时经常使用 OrderDate 范围条件来查询的特点&＃xff0c;我们最好 在 Orders.OrderDate 列上建立聚集索引&＃xff08;clustered index&＃xff09;。为了便于进行分区切换&＃xff08;partition swtich&＃xff09;&＃xff0c; 大多数情况下&＃xff0c;建议在分区表上建立分区索引。下面建立聚集分区索引&＃xff1a;

create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
on PS_Orders(OrderDate)


如果没有指定 “on PS_Orders(OrderDate)”&＃xff0c;默认建立的聚集索引和分区表的分区方案相同。

另外 Orders 分区表需要在&＃xff08;OrderID, CustomerID&＃xff09;上建立主键。我们知道主键实际上是个 唯一索引&＃xff0c;但分区表在建立唯一索引&＃xff08;分区索引&＃xff09;的时候&＃xff0c;分区列必须是唯一索引的一部分。 为什么要这样子呢&＃xff1f;因为 SQL Server 不但要保证索引在各个分区是唯一的&＃xff0c;还要保证在 整个表中是唯一的。

alter table dbo.Orders add constraint PK_Orders primary key (OrderID, CustomerID, OrderDate)


如果不指定 OrderDate 的话&＃xff0c;则会出现错误&＃xff1a;1908

Msg 1908, Level 16, State 1, Line 1
Column &＃39;OrderDate&＃39; is partitioning column of the index &＃39;PK_Orders&＃39;.
Partition columns for a unique index must be a subset of the index key.Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


查看分区表 Orders 上的索引&＃xff1a;

exec sp_helpindex &＃39;dbo.Orders&＃39;


index_name index_description index_keys
--------------------- ------------------------------------------------------ ------------------------------
IXC_Orders_OrderDate clustered located on PS_Orders OrderDate
PK_Orders nonclustered, unique, primary key located on PS_Orders OrderID, CustomerID, OrderDate


4. 向分区表中填充数据

insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDatefrom dbo.Orders_From_SQL2000_Northwind


数据表 dbo.Orders_From_SQL2000_Northwind&＃xff0c;是从 SQL Server 2000 中 Northwind.Orders 迁移过来的。

5. 查看分区表各分区数据情况&＃xff08;数据行数&＃xff0c;最大最小 OrderDate 值&＃xff09;

select partition &＃61; $partition.PF_Orders_OrderDateRange(OrderDate),rows &＃61; count(*),minval &＃61; min(OrderDate),maxval &＃61; max(OrderDate)from dbo.Ordersgroup by $partition.PF_Orders_OrderDateRange(OrderDate)order by partition


partition rows minval maxval
----------- ----------- ----------------------- -----------------------
1 152 1996-07-04 00:00:00.000 1996-12-31 00:00:00.000
2 408 1997-01-01 00:00:00.000 1997-12-31 00:00:00.000
3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000(3 row(s) affected)


从以上结果集中可以看出&＃xff1a;分区表 Orders 的 3 个分区中已经填入了数据。 这里要注意 $partition.partition_function_name(expression) 这个函数&＃xff0c; 或许像我一样&＃xff1a;一辈子没见过这样的函数&＃xff1a;&＃xff09;

本文链接&＃xff1a;http://www.sqlstudy.com/sql_article.php?id&＃61;2008071101