Sql Server系列：分區表操作

標籤：style   blog   http   io   color   ar   使用   sp   for   

1. 分區表簡介

　　分區表在邏輯上是一個表，而物理上是多個表。從使用者角度來看，分區表和普通表是一樣的。使用分區表的主要目的是為改善大型表以及具有多個訪問模式的表的延展性和可管理性。

　　分區表是把資料按設定的標準劃分成地區儲存在不同的檔案組中，使用分區可以快速而有效管理和訪問資料子集。

1.1> 適合做分區表的情況

　　◊ 資料庫中某個表的資料很多，在查詢資料時會明顯感覺到速度很慢，這個時候需要考慮分區表；

　　◊ 資料是分段的，如以年份為分隔的資料，對於當年的資料經常進行增刪改查操作，而對於往年的資料幾乎不做操作或只做查詢操作，這種情況可以使用分區表。對資料的操作如果只涉及一部分資料而不是全部資料的情況可以考慮分區表，如果一張表的資料經常使用且不管年份之類的因素經常對其增刪改查操作則最好不要分區。

1.2> 分區表的優點

　　◊ 分區表可以從物理上將一個大表分成幾個小表，但是從邏輯上來看還是一個大表。

　　◊ 對於具有多個CPU的系統，分區可以對錶的操作通過並行的方式進行，可以提升訪問效能。

2. 建立分區表步驟

　　建立分區表的步驟分為5步：

　　（1）建立資料庫檔案組

　　（2）建立資料庫檔案

　　（3）建立分區函數

　　（4）建立資料分割配置

　　（5）建立分區表

2.1> 建立資料庫檔案組

　　建立樣本資料庫Northwind，建立資料庫檔案組和檔案，添加檔案組。

　　

2.2> 建立資料庫檔案

　　建立資料檔案，並為資料檔案分配檔案組。

　　

　　完成建立後的資料庫檔案資訊

　　

2.3> 建立分區函數

　　建立分區函數Transact-SQL文法：

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

　　參數：　　

　　partition_function_name：分區函數的名稱。 分區函數名稱在資料庫內必須唯一，並且符合標識符的規則。

　　input_parameter_type：用於分區的列的資料類型。 當用作分區列時，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名資料類型或 CLR 使用者定義資料類型外，所有資料類型均有效。

　　boundary_value：為使用 partition_function_name 的資料分割資料表或索引的每個分區指定邊界值。 如果 boundary_value 為空白，則分區函數使 partition_function_name 將整個表或索引映射到單個分區。 只能使用 CREATE TABLE 或 CREATE INDEX 語句中指定的一個分區列。

　　LEFT | RIGHT 指定當間隔值由 資料庫引擎 按升序從左至右排序時，boundary_value [ ,...n ] 屬於每個邊界值間隔的哪一側（左側還是右側）。 如果未指定，則預設值為 LEFT。

　　樣本：建立將用於Order表的分區函數

CREATE PARTITION FUNCTION Function_Order ( DATETIME )AS RANGE RIGHTFOR VALUES(‘2011-01-01‘, ‘2012-01-01‘, ‘2013-01-01‘, ‘2014-01-01‘)

2.4> 建立資料分割配置

　　資料分割配置的作用是將分區函數產生的分區映射到檔案組中去，資料分割配置是讓SQL Server將已分區的資料放在哪個檔案組中。

　　在當前資料庫中建立一個將資料分割資料表或資料分割索引的分區映射到檔案組的方案。 資料分割資料表或資料分割索引的分區的個數和域在分區函數中確定。 必須首先在 CREATE PARTITION FUNCTION 語句中建立分區函數，然後才能建立資料分割配置。

　　建立資料分割配置的Transact-SQL文法：

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

　　參數：

　　partition_scheme_name：資料分割配置的名稱。 資料分割配置名稱在資料庫中必須是唯一的，並且符合標識符規則。

　　partition_function_name：使用資料分割配置的分區函數的名稱。 分區函數所建立的分區將映射到在資料分割配置中指定的檔案組。 partition_function_name 必須已經存在於資料庫中。 單個分區不能同時包含 FILESTREAM 和非 FILESTREAM 檔案組。

　　ALL：指定所有分區都映射到在 file_group_name 中提供的檔案組，或映射到主檔案組（如果指定了 [PRIMARY]。 如果指定了 ALL，則只能指定一個 file_group_name。

　　file_group_name | [ PRIMARY ] [ ,...n]：指定用來持有由 partition_function_name 指定的分區的檔案組的名稱。 file_group_name 必須已經存在於資料庫中。
　　如果指定了 [PRIMARY]，則分區將儲存於主檔案組中。 如果指定了 ALL，則只能指定一個 file_group_name。 分區分配到檔案組的順序是從分區 1 開始，按檔案組在 [,...n] 中列出的順序進行分配。 在 [,...n] 中，可以多次指定同一個 file_group_name。 如果 n 不足以擁有在 partition_function_name 中指定的分區數，則 CREATE PARTITION SCHEME 將失敗，並返回錯誤。
　　如果 partition_function_name 產生的分區數少於檔案組數，則第一個未分配的檔案組將標記為 NEXT USED，並且出現顯示命名 NEXT USED 檔案組的資訊。 如果指定了 ALL，則單獨的 file_group_name 將為該 partition_function_name 保持它的 NEXT USED 屬性。 如果在 ALTER PARTITION FUNCTION 語句中建立了一個分區，則 NEXT USED 檔案組將再接收一個分區。 若要再建立一個未分配的檔案組來擁有新的分區，請使用 ALTER PARTITION SCHEME。
　　在 file_group_name[ 1,...n] 中指定主檔案組時，必須像在 [PRIMARY] 中那樣分隔 PRIMARY，因為它是關鍵字。

 

　　樣本：建立將用於Order表的資料分割配置

CREATE PARTITION SCHEME Scheme_OrderAS PARTITION Function_OrderTO ( SECTION2010, SECTION2011, SECTION2012, SECTION2013, SECTION2014 )

　　

Sql Server系列：分區表操作