通過(guò)上2篇博文，我們了解了分區表的理論，這一節就開(kāi)始實(shí)戰。本篇博文的內容如下：
1，建立分區表
2，查詢(xún)分區
3，歸檔數據
4，添加分區
5，刪除分區
6，查看元數據
PS下：最近收到很多朋友的消息和郵件，大多是關(guān)于數據庫的問(wèn)題，沒(méi)有一一答復，由于平時(shí)工作比較忙，博客更新的比較慢，在這里說(shuō)聲抱歉。
OK，我們以一個(gè)銷(xiāo)售數據庫場(chǎng)景開(kāi)始分區表實(shí)戰。
第一步：建立我們要使用的數據庫，最重要的是建立多個(gè)文件組。
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,
MAXSIZE = 100MB,
FILEGROWTH = 10%
),
FILEGROUP FG2
(
NAME = N'File2',
FILENAME = N'C:\File2.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
),
FILEGROUP FG3
(
NAME = N'File3',
FILENAME = N'C:\File3.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'Sales_Log',
FILENAME = N'C:\Sales_Log.ldf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
)
GO
第二步：建立分區函數，這里我們建立三個(gè)分區。 how（如何對數據進(jìn)行分區）
USE Sales
GO
CREATE PARTITION FUNCTION pf_OrderDate (datetime)
AS RANGE RIGHT
FOR VALUES ('2003/01/01', '2004/01/01') --n不能超過(guò) 999，創(chuàng )建的分區數等于 n + 1
GO
第三步：創(chuàng )建分區方案，關(guān)聯(lián)到分區函數 。 where（在哪里對數據進(jìn)行分區）
USE Sales
GO
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (FG1, FG2, FG3)
GO
第四步：創(chuàng )建分區表。創(chuàng )建表并將其綁定到分區方案。這里我們建立2個(gè)表，表的結構一樣。其中OrdersHistory表用于保存歸檔數據。
USE Sales
GO
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)
GO
CREATE TABLE dbo.OrdersHistory
(
OrderID int identity(10000,1),
OrderDate datetime NOT NULL,
CustomerID int NOT NULL,
CONSTRAINT PK_OrdersHistory PRIMARY KEY (OrderID, OrderDate)
)
ON ps_OrderDate (OrderDate)
GO
通過(guò)以上四步，我們建立了分區表。接著(zhù)我們要插入一些數據，來(lái)進(jìn)行數據歸檔，分區查詢(xún)等。
向數據表中寫(xiě)入2002年的范例數據
USE Sales
GO
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)
GO
向數據表中寫(xiě)入2003年的范例數據
USE Sales
GO
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)
GO
我們可以用下面的代碼查詢(xún)這2表：
SELECT * FROM dbo.Orders
SELECT * FROM dbo.OrdersHistory
查詢(xún)的結果是Orders里面有8行數據，而OrdersHistory還沒(méi)有數據。因為我們還沒(méi)歸檔數據，所以OrdersHistory表還沒(méi)有數據。
插入完數據后，我們來(lái)做如下實(shí)驗：
1，查詢(xún)某個(gè)分區
這里我們要用到$PARTITION 函數，這個(gè)函數可以幫助我們查詢(xún)某個(gè)分區的數據，還可以檢索某個(gè)值所隸屬的分區號。$PARTITION 函數的進(jìn)一步細節可以查看MSDN
查詢(xún)已分區表Order的第一個(gè)分區，代碼如下：
SELECT *
FROM dbo.Orders
WHERE $PARTITION.pf_OrderDate(OrderDate) = 1
查詢(xún)結果只包含2002年的數據，如下圖：
如果想獲得2003年的數據，需要如下的代碼：
SELECT *
FROM dbo.Orders
WHERE $PARTITION.pf_OrderDate(OrderDate) = 2
我們還可以查詢(xún)某個(gè)分區有多少行數據，代碼如下：
SELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition,
COUNT(*) AS [COUNT]
FROM dbo.Orders
GROUP BY $PARTITION.pf_OrderDate(OrderDate)
ORDER BY Partition ;
我們還可以通過(guò)$PARTITION 函數獲得一組分區標示列值的分區號，例如獲得2002屬于哪個(gè)分區，代碼如下：
SELECT Sales.$PARTITION.pf_OrderDate('2002')
很明顯，2002年隸屬于第1個(gè)分區，因為我們建立分區函數時(shí)用了RANGE RIGHT，所以返回1。你也可以把2002年換成2003，2004，2005，2009等等測試。你會(huì )發(fā)現，2003年屬于第2個(gè)分區，2004年以后的都屬于第3個(gè)分區。
2，歸檔數據
假如現在是2003年年初，那么我們就可以把2002年所有的交易記錄歸檔到歷史訂單表HistoryOrder中。代碼如下：
USE Sales
GO
ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1
GO
此時(shí)如果我們再執行如下代碼：
SELECT * FROM dbo.Orders
SELECT * FROM dbo.OrdersHistory
便會(huì )發(fā)現，Orders 表只剩2003年的數據，而OrdersHistory表中包含了2002年的數據。
當然如果到了2004年年初，我們也可以歸檔2003年的所有交易數據。代碼如下：
USE Sales
GO
ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersHistory PARTITION 2
GO
3，添加分區
由于目前我們只有三分分區，而這三個(gè)分區的區間如下：
文件組 | 分區 | 取值范圍 |
FG1 | 1 | (過(guò)去某年, 2003/01/01) |
Fg2 | 2 | [2003/01/01, 2004/01/01) |
Fg3 | 3 | [2004/01/01,未來(lái)某年) |
所以假如到了2005年年初，我們需要為2005年的交易記錄準備分區，代碼如下：
USE Sales
GO
ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2
ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2005/01/01')
GO
ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2 用來(lái)指定新分區的數據存儲在那個(gè)文件。這里NEXT USED FG2 代表我們將新分區的數據保存在FG2文件組中，當然我們也可以在原有數據庫上新建一個(gè)文件組，把新分區的數據保存在新文件組當中，這里我們直接用FG2文件組。
ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE ('2005/01/01') 代表我們創(chuàng )建一個(gè)新分區，而這里SPLIT RANGE ('2005/01/01')正是創(chuàng )建新分區的關(guān)鍵語(yǔ)法。
執行完上面的代碼之后，我們就有了4個(gè)分區，此時(shí)的區間如下：
文件組 | 分區 | 取值范圍 |
FG1 | 1 | (過(guò)去某年, 2003/01/01) |
Fg2 | 2 | [2003/01/01, 2004/01/01) |
Fg3 | 3 | [2004/01/01, 2005/01/01) |
Fg2 | 4 | [2005/01/01, 未來(lái)某年) |
4，刪除分區
刪除分區又稱(chēng)為合并分區，假如我們想合并2002年的分區和2003年的分區到一個(gè)分區，我們可以用如下的代碼：
USE Sales
GO
ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE ('2003/01/01')
GO
執行完上面的代碼，此時(shí)分區區間如下：
文件組 | 分區 | 取值范圍 |
Fg2 | 1 | [過(guò)去某年, 2004/01/01) |
Fg3 | 2 | [2004/01/01, 2005/01/01) |
Fg2 | 3 | [2005/01/01, 未來(lái)某年) |
合并2002和2003年的數據到2003年之后，我們執行如下代碼：
SELECT Sales.$PARTITION.pf_OrderDate('2003')
你會(huì )發(fā)現返回的結果是1。而原來(lái)返回的是2，原因是2002年以前數據所在的那個(gè)分區合并到了2003年這個(gè)分區中了。
假如此時(shí)我們執行如下代碼：
SELECT *
FROM dbo.OrdersHistory
WHERE $PARTITION.pf_OrderDate(OrderDate) = 2
結果一行數據都沒(méi)返回，事實(shí)就這樣，因為OrdersHistory 表中只存儲了2002和2003年的歷史數據，在沒(méi)有合并分區之前，執行上面的代碼肯定會(huì )查詢(xún)出2003年的數據，但是合并了分區之后，上面代碼實(shí)際查詢(xún)的是第二個(gè)分區中2004年的數據。
不過(guò)當我們改成如下的代碼：
SELECT *
FROM dbo.OrdersHistory
WHERE $PARTITION.pf_OrderDate(OrderDate) = 1
便會(huì )查詢(xún)出8行數據，包括2002年和2003年的數據，因為合并分區后2002年和2003年的數據都成了第1個(gè)分區的數據了。
通過(guò)圖形我們來(lái)回憶下歸檔數據、添加分區、合并分區。
5，查看元數據
我們可以通過(guò)以下三個(gè)視圖來(lái)觀(guān)察我們創(chuàng )建的分區函數，分區方案，邊界點(diǎn)值等。
select * from sys.partition_functions
select * from sys.partition_range_values
select * from sys.partition_schemes
查詢(xún)的結果如下圖：
本文出自 “李濤的技術(shù)專(zhuān)欄” 博客，請務(wù)必保留此出處http://terryli.blog.51cto.com/704315/169601
