SQL Server 2005 分区表实践

SQL Server 
2005
 分区表实践——建立分区表（partition 
table
） 
问题：有一个订单表 Orders，要转换成分区表，以订单日期 OrderDate 为分区列，目前含有订单日期为 
1996
-
07
-
04
 
~
 
1998
-
05
-
06
 的数据。可以在 SQL Server 
2000
 Northwind 数据库中找到 Orders 表，下面是简化了的表结构： 


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

go



create
 
clustered
 
index
 IXC_Orders_OrderDate 
on
 dbo.Orders(OrderDate)

go



1
. 创建分区函数（partition 
function
） 
在创建分区函数的时候，我一般这样命名分区函数：
'
PF
'
 
+
 
Table
 Name 
+
 分区字段名 
+
 
'
Range
'
，例如：
'
PF_Orders_OrderDateRange
'
，如果字段名较长的话，则可以省略去，可以这样命名： 
'
PF_Orders_DateRange
'
 


create
 partition 
function
 PF_Orders_OrderDateRange(
datetime
)

as

range 
right
 
for
 
values
 (

'
1997-01-01
'
,

'
1998-01-01
'
,

'
1999-01-01
'

)

go


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


exec
 dbo.sp_show_partition_range 
@partition_function
 
=
 
'
PF_Orders_OrderDateRange
'


partition_function        partition   minval                    value     maxval

--
----------------------- ----------- ------------------------- --------  ------------------------


PF_Orders_OrderDateRange  
1
           
NULL
                      
<=
 val 
<
  
1997
-
01
-
01
 
00
:
00
:
00.000

PF_Orders_OrderDateRange  
2
           
1997
-
01
-
01
 
00
:
00
:
00.000
   
<=
 val 
<
  
1998
-
01
-
01
 
00
:
00
:
00.000

PF_Orders_OrderDateRange  
3
           
1998
-
01
-
01
 
00
:
00
:
00.000
   
<=
 val 
<
  
1999
-
01
-
01
 
00
:
00
:
00.000

PF_Orders_OrderDateRange  
4
           
1999
-
01
-
01
 
00
:
00
:
00.000
   
<=
 val 
<
  
NULL


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


2
. 创建分区方案（partition scheme） 
分区方案定义了，分区表或者分区索引的每个分区的数据存放在哪个文件组上。试想，如果没有分区方案的话，如何实现把分区表中不同的分区存放在不同的文件组上？我们可以看下创建普通数据表的语法： 


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


显然，普通表整个表的数据，只能存放在同一个文件组上。为了实现分区机制，才引入了分区方案这个概念。每个分区表只属于一个方案（scheme），因此分区方案可以这样命名：
'
PS
'
 
+
 
Table
 Name 


create
 partition scheme PS_Orders

as

partition PF_Orders_OrderDateRange

to
 (
[
primary
]
, 
[
primary
]
, 
[
primary
]
, 
[
primary
]
)

go


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

下面的例子，分区方案指定了 
5
 个文件组（多出了 
1
 个文件组）。 


drop
 partition scheme PS_Orders

go



create
 partition scheme PS_Orders

as

partition PF_Orders_OrderDateRange

to
 (
[
primary
]
, 
[
primary
]
,
[
primary
]
, 
[
primary
]
,
[
primary
]
)

go


Partition scheme 
'
PS_Orders
'
 has been created successfully.

'
PRIMARY
'
 
is
 marked 
as
 the 
next
 used filegroup 
in
 partition scheme 
'
PS_Orders
'
.

下面的例子，分区方案指定了 
7
 个文件组（多出了 
3
 个文件组）。 


drop
 partition scheme PS_Orders

go



create
 partition scheme PS_Orders

as

partition PF_Orders_OrderDateRange

to
 (
[
primary
]
, 
[
primary
]
,
[
primary
]
, 
[
primary
]
,
[
primary
]
,
[
primary
]
,
[
primary
]
)

go


Partition scheme 
'
PS_Orders
'
 has been created successfully.

'
PRIMARY
'
 
is
 marked 
as
 the 
next
 used filegroup 
in
 partition scheme 
'
PS_Orders
'
.

2
 filegroup specified after the 
next
 used filegroup are ignored.

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


drop
 partition scheme PS_Orders

go



create
 partition scheme PS_Orders

as

partition PF_Orders_OrderDateRange

all
 
to
 (
[
primary
]
)

go



3
. 创建分区表（partition 
table
） 
创建了分区函数和分区方案，准备工作做完了，现在终于可以开始创建分区表了。创建分区表和普通表的语法大致相同，不同之处：普通表需要指定所存放的文件组，分区表需要指定分区方案。 


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 范围条件来查询的特点，我们最好在 Orders.OrderDate 列上建立聚集索引（
clustered
 
index
）。为了便于进行分区切换（partition swtich），大多数情况下，建议在分区表上建立分区索引。下面建立聚集分区索引： 


create
 
clustered
 
index
 IXC_Orders_OrderDate 
on
 dbo.Orders(OrderDate)

on
 PS_Orders(OrderDate)

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

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


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

如果不指定 OrderDate 的话，则会出现错误：
1908
 

Msg 
1908
, 
Level
 
16
, State 
1
, Line 
1


Column
 
'
OrderDate
'
 
is
 partitioning 
column
 
of
 the 
index
 
'
PK_Orders
'
.
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 上的索引： 


exec
 sp_helpindex 
'
dbo.Orders
'


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, OrderDate
  
from
 dbo.Orders_From_SQL2000_Northwind

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


5
. 查看分区表各分区数据情况（数据行数，最大最小 OrderDate 值） 

select
 partition 
=
 $partition.PF_Orders_OrderDateRange(OrderDate)
      ,rows      
=
 
count
(
*
)
      ,minval    
=
 
min
(OrderDate)
      ,maxval    
=
 
max
(OrderDate)
  
from
 dbo.Orders
 
group
 
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)

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

 

本文由 www.sqlstudy.com 原创，版权所有，转载请注明作者和出处！

本文链接：http:
//
www.sqlstudy.com
/
sql_article.php?id
=
2008071101