/***************************************************************
BIWORK 分區表閱讀與實踐筆記
Note: 示例中使用到了SQL Server 2000的 Demo Database, 可以從此鏈接中下載
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654
在檢查刪除Partition Function 以及Partition Scheme 時,要注意
Partition Scheme 引用了Partition Function, 所有需要先刪除
Partition Scheme. 同理,引用了Partition Scheme 的表應該先刪除掉.
引用關系: TABLE -> PARTITION SCHEME -> PARTITION FUNCTION
***************************************************************/
IF OBJECT_ID('dbo.Orders')IS NOT NULL
DROP TABLE Orders
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_Orders')
DROP PARTITION SCHEME PS_Orders
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PF_Orders_OrderDateRange')
DROP PARTITION FUNCTION PF_Orders_OrderDateRange
GO
/***************************************************************
1. 如果在不需要對數據庫進行物理分組的情況下,比如分區表還是享用同
一個文件組,那麼應該可以從創建分區函數開始
*** 創建分區函數
*** 確定分區鍵列的類型(DATETIME)以及分區的邊界值:
(''1997-01-01','1998-01-01','1999-01-01'')
*** N個邊界值確定N+1 個分區
*** RIGHT - 第一個分區的所有值都小於VAL < 1997-01-1
第二個分區的值范圍是1997-01-01 <= VAL < 1998-01-01
***************************************************************/
CREATE PARTITION FUNCTION PF_Orders_OrderDateRange(DATETIME)
AS
RANGE RIGHT FOR VALUES
(
'1997-01-01',
'1998-01-01',
'1999-01-01'
)
GO
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
****************************************************************/
/***************************************************************
2. 創建了分區函數後,便可以創建分區方案
*** 因為在上一個分區函數中有個邊界值,4個分區,並且並沒有其它的
數據庫文件組,所以當分區方案應用到具體的分區函數時所有的分區
都是指向PRIMARY 文件組
***************************************************************/
CREATE PARTITION SCHEME PS_Orders
AS
PARTITION PF_Orders_OrderDateRange
TO ([primary],[primary],[primary],[primary])
GO
/***************************************************************
3. 創建分區表時要應用分區方案,並提供具體的分區鍵列
ON 分區函數(分區鍵列)
****************************************************************/
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
/******************************************************************
4. 在創建分區表後,需要創建聚集分區索引
*** 根據訂單表Orders 查詢時經常使用OrderDate 范圍條件來查詢的特點,
*** 我們最好在Orders.OrderDate 列上建立聚集索引（clustered index）.
*** 為了便於進行分區切換（partition swtich)
大多數情況下,建議在分區表上建立分區索引。
*******************************************************************/
CREATE CLUSTERED INDEX IXC_Orders_OrderDate ON dbo.Orders(OrderDate)
GO
/*******************************************************************
5. 為分區表創建主鍵
如果主鍵不包含分區鍵列,將會出現以下錯誤信息:
Msg 1908, Level 16, State 1, Line 2
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 2
Could not create constraint. See previous errors.
原因:
主鍵實際上是個唯一索引,但分區表在建立唯一索引（分區索引）的時候,
分區列必須是唯一索引的一部分.因為SQL Server 不但要保證索引在各個
分區是唯一的,還要保證在整個表中是唯一的.
********************************************************************/
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
/************************************************************************
查看分區表Orders 上的索引:
IXC_Orders_OrderDate|clustered located on PS_Orders|OrderDate
PK_Orders|nonclustered,unique,primary key located on PS_Orders|OrderID, CustomerID,
OrderDate
************************************************************************/
EXEC sp_helpindex 'dbo.Orders'
/**********************************************************************
6. 從SQL Server 2000 NorthWind 導入測試數據
***********************************************************************/
INSERT INTO dbo.Orders
SELECT OrderID,
CustomerID,
EmployeeID,
OrderDate
FROM Northwind.dbo.Orders
/************************************************************************
7. 查看分區表各分區數據情況（數據行數，最大最小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
GO
/************************************************************************
在目前的測試數據中,並沒有大於1999年的數據,所以在上面的查詢結果中並沒有
看到第個分區的信息: 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
*************************************************************************/
-- 插入一條測試數據
INSERT INTO dbo.Orders VALUES(11111,'TEST',1,'2000-10-10 10:10:10:100')
-- 再次查詢
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
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
*************************************************************************/
GO
/*************************************************************************
************* 切換分區表的一個分區到普通數據表***************************
************* Partition to Table ****************************************/
/*************************************************************************
1. 首先建立普通數據表Orders_1998，該表用來存放訂單日期為1998 年的所有數據
2. 分區到普通表的切換，最好滿足以下的前提條件:
a. 普通表必須建立在分區表切換分區所在的文件組上ON [PRIMARY]
b. 普通表的表結構跟分區表的一致
c. 普通表上的索引要跟分區表一致(聚集索引,非聚集索引)
d. 普通表必須是空表，不能有任何數據
*************************************************************************/
IF OBJECT_ID('Orders_1998') IS NOT NULL
DROP TABLE Orders_1998
GO
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(OrderID,CustomerID,OrderDate)
GO
/***************************************************************************
** 開始切換分區表Orders 第三個分區的數據(1998年的數據)到普通表Orders_1998
** 關鍵字- SWITCH PARTITION [NUMBER] TO [History Table]
***************************************************************************/
ALTER TABLE dbo.Orders SWITCH PARTITION 3 TO dbo.Orders_1998
/***************************************************************************
查詢源分區表結果
分區號為的數據已經沒有了
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
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查詢存檔表結果
3 270 1998-01-01 00:00:00.000 1998-05-06 00:00:00.000
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders_1998
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/************************************************************************
************* 切換普通表數據到分區表的一個分區中 *********************
************* Table to Partition ************************/
/*************************************************************************
上面我們已經把分區表Orders 第三個分區的數據切換到普通表Orders_1998 中了,
現在我們再切換回來:
**************************************************************************/
ALTER TABLE dbo.Orders_1998 SWITCH PARTITION 3 TO dbo.Orders
/*************************************************************************
錯誤信息:
Msg 4911, Level 16, State 2, Line 1
Cannot specify a partitioned table without partition number in ALTER TABLE
SWITCH statement. The table 'SSISDemoDB.dbo.Orders' is partitioned.
原因:
實際上應該是將dbo.Orders_1998 表中的數據SWITCH 到dbo.Orders 表的
Partition 分區中.
而不能說是將dbo.Orders_1998 的分區的數據SWITCH 到dbo.Orders 全表中
**************************************************************************/
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
'dbo.Orders_1998' allow values that are not allowed by range defined by
partition 3 on target table 'dbo.Orders'.
原因:
表dbo.Orders 的數據經過分區函數的分區列定義, 各個分區的數據實際上已經經過
了數據約束檢查,符合分區邊界范圍(Range)的數據才會錄入到各個分區中.
但是在歷史表/存檔表dbo.Orders_1998中的數據實際上是沒有邊界約束的,比如完全
可以手動的插入一條年的數據,這樣一來在進行SWITCH時肯定是不會成功的.
所以在SWITCH時,先進行了約束性檢查,盡管沒有不符合規范的數據,但是有潛在的威脅.
所以在SWITCH之前,先為dbo.Orders_1998添加一個檢查約束,並再次SWITCH,成功！
**************************************************************************/
ALTER TABLE dbo.Orders_1998 ADD CONSTRAINT CK_Orders1998_OrderDate
CHECK(OrderDate>='1998-01-01' AND OrderDate<'1999-01-01')
ALTER TABLE dbo.Orders_1998 SWITCH TO dbo.Orders PARTITION 3
/***************************************************************************
查詢源分區表結果,分區的數據已經從dbo.Orders_1998 回來了
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
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查詢存檔表結果,沒有任何數據,已經成功SWITCH to Orders 表的PARTITION 3
***************************************************************************/
SELECT PARTITION = $PARTITION.PF_Orders_OrderDateRange(OrderDate),
ROWS = COUNT(*),
MinVal = MIN(OrderDate),
MaxVal = MAX(OrderDate)
FROM dbo.Orders_1998
GROUP BY $PARTITION.PF_Orders_OrderDateRange(OrderDate)
ORDER BY PARTITION
/****************************************************************************
所以在進行存檔表的數據向分區表遷移過程中(TABLE TO PARTITION),
相比(PARTITION TO TABLE)多一個條件:
普通表必須加上和分區數據范圍一致的約束條件.
*****************************************************************************/
/************************************************************************
************* 切換分區表數據到分區表 ********************************
************* PARTITION TO PARTITION **********************************/
/*************************************************************************
-- 新的存檔分區表在結構上和源分區表是一致的,包括分區函數和分區方案,
但是需要重新創建,不能簡單地直接使用dbo.Orders 表上的分區函數和分區方案,
因為他們之間有綁定關系.
**************************************************************************/
IF OBJECT_ID('OrdersArchive') IS NOT NULL
DROP TABLE OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PS_OrdersArchive')
DROP PARTITION SCHEME PS_OrdersArchive
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name =
'PF_OrdersArchive_OrderDateRange')
DROP PARTITION FUNCTION PF_OrdersArchive_OrderDateRange
GO
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
-- 分區Scheme和分區函數綁定了
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
)
-- 表和分區Scheme綁定了
ON PS_OrdersArchive(OrderDate)
GO
CREATE CLUSTERED INDEX IXC_OrdersArchive_OrderDate ON dbo.OrdersArchive(OrderDate)
ALTER TABLE dbo.OrdersArchive ADD CONSTRAINT PK_OrdersArchive
PRIMARY KEY(OrderID, CustomerID,OrderDate)
GO
/*********************************************************************************
開始切換分區
**********************************************************************************/
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
/***************************************************************************
查詢源分區表結果,只會有分區的數據
4 1 2000-10-10 10:10:10.100 2000-10-10 10:10:10.100
***************************************************************************/
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
/***************************************************************************
查詢存檔表結果,已經成功轉移
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
***************************************************************************/
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 普通表,
以實現快速刪除分區表數據,快速歸檔不活躍數據到歷史表。
****************************************************************************/
注 : 已經不記得原博客地址了, 這篇日志是基於別人的分析成果之上加上自己親自實踐, 思考, 重 新添加了一些代碼和注釋. 在注解和結論驗證方面按照自己的理解做出了還算比較細致的說明,對剛接觸 表分區概念的朋友們相信會有很大的幫助. 我也是通過這種方式學習和掌握了表分區的一些基礎操作步 驟和處理方式.
