SQL Server 2005分区表——分区切换
http://blog.csdn.net/liangCK/archive/2009/01/08/3733670.aspx
本文演示了SQL Server 2005 分区表分区切换的三种形式：1. 切换分区表的一个分区到普通数据表中：Partition to Table；2. 切换普通表数据到分区表的一个分区中：Table to Partition；3. 切换分区表的分区到另一分区表：Partition to Partition。并指出了在分区表分区切换过程中的注意事项。
-- 创建分区函数
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- 创建分区方案
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
-- 创建分区表
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
-- 创建聚集分区索引
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
-- 为分区表设置主键
alter table dbo.Orders add constraint PK_Orders
primary key (OrderID, CustomerID, OrderDate)
go
-- 导入数据到分区表
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
from dbo.Orders_From_SQL2000_Northwind --（注：数据来源于SQL Server 2000 示例数据库）
go
-- 查看分区表每个分区的数据分布情况
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
go
一、切换分区表的一个分区到普通数据表中：Partition to Table
首先建立普通数据表Orders_1998，该表用来存放订单日期为1998 年的所有数据。
create table dbo.Orders_1998
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
) on [primary]
go
create clustered index IXC_Orders1998_OrderDate on dbo.Orders_1998(OrderDate)
go
alter table dbo.Orders_1998 add constraint PK_Orders_1998
primary key nonclustered (OrderID, CustomerID, OrderDate)
go
开始切换分区表Orders 第三个分区的数据（年的数据）到普通表Orders_1998
alter table dbo.Orders switch partition 3 to dbo.Orders_1998
值得注意的是，如果你想顺利地进行分区到普通表的切换，最好满足以下的前提条件：1. 普通表必须建立在分区表切换分区所在的文件组上。2. 普通表的表结构跟分区表的一致；3. 普通表上的索引要跟分区表一致。4. 普通表必须是空表，不能有任何数据。
二、切换普通表数据到分区表的一个分区中：Table to Partition
上面我们已经把分区表Orders 第三个分区的数据切换到普通表Orders_1998 中了，现在我们再切换回来：
alter table dbo.Orders_1998 switch to dbo.Orders partition 3
但是，此时有错误发生：
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed.
Check constraints of source table 'Sales.dbo.Orders_1998' allow values
that are not allowed by range defined by partition 3 on target table 'Sales.dbo.Orders'.
这就奇怪了，能把数据从分区切换进来却切换不出去。出错信息中提示我们是普通表的check constraint 跟分区表不一致。于是在普通表上建立check constraint：
alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate
check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')
再次进行切换，成功！
看来，切换普通表数据到分区，除了满足上面的4 个条件外，还要加上一条：普通表必须加上和分区数据范围一致的check 约束条件。
三、切换分区表的分区到另一分区表：Partition to Partition
首先建立分区表OrdersArchive，这个表用来存放订单历史数据。
-- 创建分区函数
create partition function PF_OrdersArchive_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- 创建分区方案
create partition scheme PS_OrdersArchive
as
partition PF_OrdersArchive_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
-- 创建分区表
create table dbo.OrdersArchive
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_OrdersArchive(OrderDate)
go
-- 创建聚集分区索引
create clustered index IXC_OrdersArchive_OrderDate on dbo.OrdersArchive(OrderDate)
go
-- 为分区表设置主键
alter table dbo.OrdersArchive add constraint PK_OrdersArchive
primary key (OrderID, CustomerID, OrderDate)
go
然后，切换分区表Orders 分区数据到OrdersArchive 分区：
alter table dbo.Orders switch partition 1 to dbo.OrdersArchive partition 1
alter table dbo.Orders switch partition 2 to dbo.OrdersArchive partition 2
alter table dbo.Orders switch partition 3 to dbo.OrdersArchive partition 3
最后，查看分区表OrdersArchive 各分区数据分布情况：
-- 查看分区表每个分区的数据分布情况
select partition = $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.OrdersArchive
group by $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
order by partition
实际上，分区表分区切换并没有真正去移动数据，而是SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表。卸载分区数据到普通表，然后truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表。
本文演示了SQL Server 2005 分区表分区切换的三种形式：1. 切换分区表的一个分区到普通数据表中：Partition to Table；2. 切换普通表数据到分区表的一个分区中：Table to Partition；3. 切换分区表的分区到另一分区表：Partition to Partition。并指出了在分区表分区切换过程中的注意事项。
-- 创建分区函数
create partition function PF_Orders_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- 创建分区方案
create partition scheme PS_Orders
as
partition PF_Orders_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
-- 创建分区表
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
-- 创建聚集分区索引
create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)
go
-- 为分区表设置主键
alter table dbo.Orders add constraint PK_Orders
primary key (OrderID, CustomerID, OrderDate)
go
-- 导入数据到分区表
insert into dbo.Orders
select OrderID, CustomerID, EmployeeID, OrderDate
from dbo.Orders_From_SQL2000_Northwind --（注：数据来源于SQL Server 2000 示例数据库）
go
-- 查看分区表每个分区的数据分布情况
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
go
一、切换分区表的一个分区到普通数据表中：Partition to Table
首先建立普通数据表Orders_1998，该表用来存放订单日期为1998 年的所有数据。
create table dbo.Orders_1998
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
) on [primary]
go
create clustered index IXC_Orders1998_OrderDate on dbo.Orders_1998(OrderDate)
go
alter table dbo.Orders_1998 add constraint PK_Orders_1998
primary key nonclustered (OrderID, CustomerID, OrderDate)
go
开始切换分区表Orders 第三个分区的数据（年的数据）到普通表Orders_1998
alter table dbo.Orders switch partition 3 to dbo.Orders_1998
值得注意的是，如果你想顺利地进行分区到普通表的切换，最好满足以下的前提条件：1. 普通表必须建立在分区表切换分区所在的文件组上。2. 普通表的表结构跟分区表的一致；3. 普通表上的索引要跟分区表一致。4. 普通表必须是空表，不能有任何数据。
二、切换普通表数据到分区表的一个分区中：Table to Partition
上面我们已经把分区表Orders 第三个分区的数据切换到普通表Orders_1998 中了，现在我们再切换回来：
alter table dbo.Orders_1998 switch to dbo.Orders partition 3
但是，此时有错误发生：
Msg 4982, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed.
Check constraints of source table 'Sales.dbo.Orders_1998' allow values
that are not allowed by range defined by partition 3 on target table 'Sales.dbo.Orders'.
这就奇怪了，能把数据从分区切换进来却切换不出去。出错信息中提示我们是普通表的check constraint 跟分区表不一致。于是在普通表上建立check constraint：
alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate
check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')
再次进行切换，成功！
看来，切换普通表数据到分区，除了满足上面的4 个条件外，还要加上一条：普通表必须加上和分区数据范围一致的check 约束条件。
三、切换分区表的分区到另一分区表：Partition to Partition
首先建立分区表OrdersArchive，这个表用来存放订单历史数据。
-- 创建分区函数
create partition function PF_OrdersArchive_OrderDateRange(datetime)
as
range right for values (
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
go
-- 创建分区方案
create partition scheme PS_OrdersArchive
as
partition PF_OrdersArchive_OrderDateRange
to ([primary], [primary], [primary], [primary])
go
-- 创建分区表
create table dbo.OrdersArchive
(
OrderID int not null
,CustomerID varchar(10) not null
,EmployeeID int not null
,OrderDate datetime not null
)
on PS_OrdersArchive(OrderDate)
go
-- 创建聚集分区索引
create clustered index IXC_OrdersArchive_OrderDate on dbo.OrdersArchive(OrderDate)
go
-- 为分区表设置主键
alter table dbo.OrdersArchive add constraint PK_OrdersArchive
primary key (OrderID, CustomerID, OrderDate)
go
然后，切换分区表Orders 分区数据到OrdersArchive 分区：
alter table dbo.Orders switch partition 1 to dbo.OrdersArchive partition 1
alter table dbo.Orders switch partition 2 to dbo.OrdersArchive partition 2
alter table dbo.Orders switch partition 3 to dbo.OrdersArchive partition 3
最后，查看分区表OrdersArchive 各分区数据分布情况：
-- 查看分区表每个分区的数据分布情况
select partition = $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
,rows = count(*)
,minval = min(OrderDate)
,maxval = max(OrderDate)
from dbo.OrdersArchive
group by $partition.PF_OrdersArchive_OrderDateRange(OrderDate)
order by partition
实际上，分区表分区切换并没有真正去移动数据，而是SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表。卸载分区数据到普通表，然后truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表。
