T-SQL查詢進階--理解SQL SERVER中的分區表

簡介

分區表是在SQL SERVER2005之后的版本引入的特性。這個特性允許把邏輯上的一個表在物理上分為很多部分。而對于SQL SERVER2005之前版本，所謂的分區表僅僅是分布式視圖,也就是多個表做union操作.

分區表在邏輯上是一個表，而物理上是多個表.這意味著從用戶的角度來看，分區表和普通表是一樣的。這個概念可以簡單如下圖所示：

而對于SQL SERVER2005之前的版本，是沒有分區這個概念的，所謂的分區僅僅是分布式視圖:

本篇文章所講述的分區表指的是SQL SERVER2005之后引入的分區表特性.

為什么要對表進行分區

在回答標題的問題之前，需要說明的是，表分區這個特性只有在企業版或者開發版中才有,還有理解表分區的概念還需要理解SQL SERVER中文件和文件組的概念.

對表進行分區在多種場景下都需要被用到.通常來說，使用表分區最主要是用于:

存檔，比如將銷售記錄中1年前的數據分到一個專門存檔的服務器中
便于管理，比如把一個大表分成若干個小表，則備份和恢復的時候不再需要備份整個表，可以單獨備份分區
提高可用性，當一個分區跪了以后，只有一個分區不可用，其它分區不受影響
提高性能，這個往往是大多數人分區的目的，把一個表分布到不同的硬盤或其他存儲介質中，會大大提升查詢的速度.

分區表的步驟

分區表的定義大體上分為三個步驟：

定義分區函數
定義分區構架
定義分區表

分區函數，分區構架和分區表的關系如下：

分區表依賴分區構架，而分區構架又依賴分區函數.值得注意的是，分區函數并不屬于具體的分區構架和分區表，他們之間的關系僅僅是使用關系.

下面我們通過一個例子來看如何定義一個分區表:

假設我們需要定義的分區表結構如下:

第一列為自增列，orderid為訂單id列，SalesDate為訂單日期列，也就是我們需要分區的依據.

下面我們按照上面所說的三個步驟來實現分區表.

定義分區函數

分區函數是用于判定數據行該屬于哪個分區,通過分區函數中設置邊界值來使得根據行中特定列的值來確定其分區，上面例子中，我們可以通過SalesDate的值來判定其不同的分區.假設我們想定義兩個邊界值(boundaryValue)進行分區,則會生成三個分區,這里我設置邊界值分別為2004-01-01和2007-01-01，則前面例子中的表會根據這兩個邊界值分成三個區:

在MSDN中，定義分區函數的原型如下:

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

通過定義分區函數的原型，我們看出其中并沒有具體涉及具體的表.因為分區函數并不和具體的表相綁定.上面原型中還可以看到Range left和right.這個參數是決定臨界值本身應該歸于“left”還是“right”：

下面我們根據上面的參數定義分區函數:

通過系統視圖，可以看見這個分區函數已經創建成功

定義分區構架

定義完分區函數僅僅是知道了如何將列的值區分到了不同的分區。而每個分區的存儲方式，則需要分區構架來定義.使用分區構架需要你對文件和文件組有點了解.

我們先來看MSDN的分區構架的原型:

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

從原型來看，分區構架僅僅是依賴分區函數.分區構架中負責分配每個區屬于哪個文件組，而分區函數是決定如何在邏輯上分區:

基于之前創建的分區函數,創建分區構架:

定義分區表

接下來就該創建分區表了.表在創建的時候就已經決定是否是分區表了。雖然在很多情況下都是你在發現已經表已經足夠大的時候才想到要把表分區，但是分區表只能夠在創建的時候指定為分區表。

為剛建立的分區表PartitionedTable加入5萬條測試數據，其中SalesDate隨機生成，從2001年到2010年隨機分布.加入數據后，我們通過如下語句來看結果:

select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(10), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('PartitionedTable')
and i.index_id in (0, 1) 
order by p.partition_number