hsiung.博格 ERP軟體

本文演示了 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 第三個分區的資料（1998年的資料）到普通表 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 普通表，以實現快速刪除分區表資料。快速歸檔不活躍資料到歷史表。

原文地址：http://www.sqlstudy.com/sql_article