實戰分區表：SQL Server 2k5&2k8系列(三)

通過上2篇博文，我們了解了分區表的理論，這一節就開始實戰。本篇博文的内容如下：

1，建立分區表

2，查詢分區

3，歸檔資料

4，添加分區

5，删除分區

6，檢視中繼資料

PS下：最近收到很多朋友的消息和郵件，大多是關于資料庫的問題，沒有一一答複，由于平時工作比較忙，部落格更新的比較慢，在這裡說聲抱歉。

OK，我們以一個銷售資料庫場景開始分區表實戰。

第一步：建立我們要使用的資料庫，最重要的是建立多個檔案組。

CREATE DATABASE Sales ON PRIMARY 

(    

  NAME = N'Sales',    

  FILENAME = N'C:\Sales.mdf',    

  SIZE = 3MB, 

  MAXSIZE = 100MB,    

  FILEGROWTH = 10%    

),    

FILEGROUP FG1    

  NAME = N'File1',    

  FILENAME = N'C:\File1.ndf',    

  SIZE = 1MB,    

FILEGROUP FG2    

  NAME = N'File2',    

  FILENAME = N'C:\File2.ndf',    

  MAXSIZE = 100MB, 

), 

FILEGROUP FG3    

  NAME = N'File3',    

  FILENAME = N'C:\File3.ndf',    

)    

LOG ON    

  NAME = N'Sales_Log',    

  FILENAME = N'C:\Sales_Log.ldf',    

  FILEGROWTH = 10% 

) 

GO

第二步：建立分區函數，這裡我們建立三個分區。 how（如何對資料進行分區）

USE Sales    

GO 

CREATE PARTITION FUNCTION pf_OrderDate (datetime)    

AS RANGE RIGHT    

FOR VALUES ('2003/01/01', '2004/01/01') --n不能超過 999，建立的分區數等于 n + 1 

第三步：建立分區方案，關聯到分區函數 。 where（在哪裡對資料進行分區）

GO    

CREATE PARTITION SCHEME ps_OrderDate    

AS PARTITION pf_OrderDate    

TO (FG1, FG2, FG3)    

第四步：建立分區表。建立表并将其綁定到分區方案。這裡我們建立2個表，表的結構一樣。其中OrdersHistory表用于儲存歸檔資料。

CREATE TABLE dbo.Orders    

  OrderID int identity(10000,1),    

  OrderDate datetime NOT NULL,    

  CustomerID int NOT NULL,    

  CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)    

ON ps_OrderDate (OrderDate)    

CREATE TABLE dbo.OrdersHistory    

  CONSTRAINT PK_OrdersHistory PRIMARY KEY (OrderID, OrderDate)    

通過以上四步，我們建立了分區表。接着我們要插入一些資料，來進行資料歸檔，分區查詢等。

向資料表中寫入2002年的範例資料

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/6/25', 1000)    

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/13', 1000)    

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/8/25', 1000)    

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2002/9/23', 1000) 

向資料表中寫入2003年的範例資料

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/6/25', 1000) 

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/13', 1000) 

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/8/25', 1000) 

INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2003/9/23', 1000)    

我們可以用下面的代碼查詢這2表：

SELECT * FROM dbo.Orders    

SELECT * FROM dbo.OrdersHistory

查詢的結果是Orders裡面有8行資料，而OrdersHistory還沒有資料。因為我們還沒歸檔資料，是以OrdersHistory表還沒有資料。

插入完資料後，我們來做如下實驗：

1，查詢某個分區

這裡我們要用到$PARTITION 函數，這個函數可以幫助我們查詢某個分區的資料，還可以檢索某個值所隸屬的分區号。$PARTITION 函數的進一步細節可以檢視MSDN

查詢已分區表Order的第一個分區，代碼如下：

SELECT * 

FROM dbo.Orders 

WHERE $PARTITION.pf_OrderDate(OrderDate) = 1

查詢結果隻包含2002年的資料，如下圖：

如果想獲得2003年的資料，需要如下的代碼：

WHERE $PARTITION.pf_OrderDate(OrderDate) = 2

我們還可以查詢某個分區有多少行資料，代碼如下：

SELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition, 

COUNT(*) AS [COUNT]    

FROM dbo.Orders    

GROUP BY $PARTITION.pf_OrderDate(OrderDate)    

ORDER BY Partition ;

我們還可以通過$PARTITION 函數獲得一組分區标示列值的分區号，例如獲得2002屬于哪個分區，代碼如下：

SELECT Sales.$PARTITION.pf_OrderDate('2002')

很明顯，2002年隸屬于第1個分區，因為我們建立分區函數時用了RANGE RIGHT，是以傳回1。你也可以把2002年換成2003，2004，2005，2009等等測試。你會發現，2003年屬于第2個分區，2004年以後的都屬于第3個分區。

2，歸檔資料

假如現在是2003年年初，那麼我們就可以把2002年所有的交易記錄歸檔到曆史訂單表HistoryOrder中。代碼如下：

ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1 

此時如果我們再執行如下代碼：

SELECT * FROM dbo.Orders         

便會發現，Orders 表隻剩2003年的資料，而OrdersHistory表中包含了2002年的資料。

當然如果到了2004年年初，我們也可以歸檔2003年的所有交易資料。代碼如下：

ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersHistory PARTITION 2 

3，添加分區

由于目前我們隻有三分分區，而這三個分區的區間如下：

檔案組

分區

取值範圍

FG1

1

(過去某年, 2003/01/01)

Fg2

2

[2003/01/01, 2004/01/01)

Fg3

3

[2004/01/01,未來某年)

是以假如到了2005年年初，我們需要為2005年的交易記錄準備分區，代碼如下：

ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2    

ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2005/01/01') 

ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2 用來指定新分區的資料存儲在那個檔案。這裡NEXT USED FG2 代表我們将新分區的資料儲存在FG2檔案組中，當然我們也可以在原有資料庫上建立一個檔案組，把新分區的資料儲存在新檔案組當中，這裡我們直接用FG2檔案組。

ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2005/01/01') 代表我們建立一個新分區，而這裡SPLIT RANGE ('2005/01/01')正是建立新分區的關鍵文法。

執行完上面的代碼之後，我們就有了4個分區，此時的區間如下：

[2004/01/01, 2005/01/01)

4

[2005/01/01, 未來某年)

4，删除分區

删除分區又稱為合并分區，假如我們想合并2002年的分區和2003年的分區到一個分區，我們可以用如下的代碼：

ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('2003/01/01') 

執行完上面的代碼，此時分區區間如下：

[過去某年, 2004/01/01)

合并2002和2003年的資料到2003年之後，我們執行如下代碼：

SELECT Sales.$PARTITION.pf_OrderDate('2003')

你會發現傳回的結果是1。而原來傳回的是2，原因是2002年以前資料所在的那個分區合并到了2003年這個分區中了。

假如此時我們執行如下代碼：

FROM dbo.OrdersHistory    

結果一行資料都沒傳回，事實就這樣，因為OrdersHistory 表中隻存儲了2002和2003年的曆史資料，在沒有合并分區之前，執行上面的代碼肯定會查詢出2003年的資料，但是合并了分區之後，上面代碼實際查詢的是第二個分區中2004年的資料。

不過當我們改成如下的代碼：

便會查詢出8行資料，包括2002年和2003年的資料，因為合并分區後2002年和2003年的資料都成了第1個分區的資料了。

通過圖形我們來回憶下歸檔資料、添加分區、合并分區。

5，檢視中繼資料

我們可以通過以下三個視圖來觀察我們建立的分區函數，分區方案，邊界點值等。

select * from sys.partition_functions    

select * from sys.partition_range_values 

select * from sys.partition_schemes

查詢的結果如下圖：

本文轉自terryli51CTO部落格，原文連結： http://blog.51cto.com/terryli/169601，如需轉載請自行聯系原作者