一、分區(qū)表簡(jiǎn)介
使用分區(qū)表的主要目的，是為了改善大型表以及具有各種訪問(wèn)模式的表的可伸縮性和可管理性
大型表：數(shù)據(jù)量巨大的表。
訪問(wèn)模式：因目的不同，需訪問(wèn)的不同的數(shù)據(jù)行集，每種目的的訪問(wèn)可以稱之為一種訪問(wèn)模式。
分區(qū)一方面可以將數(shù)據(jù)分為更小、更易管理的部分，為提高性能起到一定的作用；另一方面，對(duì)于如果具有多個(gè)CPU的系統(tǒng)，分區(qū)可以是對(duì)表的操作通過(guò)并行的方式進(jìn)行，這對(duì)于提升性能是非常有幫助的。
注意：只有 SQL Server Enterprise Edition 支持分區(qū)。
二、創(chuàng)建分區(qū)表或分區(qū)索引的步驟
可以分為以下步驟：
1. 確定分區(qū)列和分區(qū)數(shù)
2. 確定是否使用多個(gè)文件組
3. 創(chuàng)建分區(qū)函數(shù)
4. 創(chuàng)建分區(qū)架構(gòu)（Schema）
5. 創(chuàng)建分區(qū)表
6. 創(chuàng)建分區(qū)索引
三、詳細(xì)步驟
1.確定分區(qū)列和分區(qū)數(shù)
在開(kāi)始做分區(qū)操作之前，首先要確定待分區(qū)表的訪問(wèn)模式，該模式?jīng)Q定了什么列適合做分區(qū)鍵。例如，對(duì)于銷售數(shù)據(jù)，一般會(huì)先根據(jù)日期把數(shù)據(jù)范圍限定在一個(gè)范圍內(nèi)，然后在這個(gè)基礎(chǔ)上做進(jìn)一步的查詢，這樣，就可以把日期作為分區(qū)列。確定了分區(qū)列之后，需要進(jìn)一步確定分區(qū)數(shù)，亦即分區(qū)表中需要包含多少數(shù)據(jù)，每個(gè)分區(qū)的數(shù)據(jù)應(yīng)該限定在哪個(gè)范圍。
2. 確定是否使用多個(gè)文件組
為了有助于優(yōu)化性能和維護(hù)，應(yīng)該使用文件組分離數(shù)據(jù)。一般情況下，如果經(jīng)常對(duì)分區(qū)的整個(gè)數(shù)據(jù)集操作，則文件組數(shù)
最好與分區(qū)數(shù)相同，并且這些文件組通常應(yīng)該位于不同的磁盤(pán)上，再配合多個(gè)CPU，則SQL Server
可以并行處理多個(gè)分區(qū)，從而大大縮短處理大量復(fù)雜報(bào)表和分析的總體時(shí)間。
3.創(chuàng)建分區(qū)函數(shù)
分區(qū)函數(shù)用于定義分區(qū)的邊界條件，創(chuàng)建分區(qū)函數(shù)的語(yǔ)法如下：
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type ) AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] )
參數(shù)說(shuō)明：
partition_function_name
是分區(qū)函數(shù)的名稱。分區(qū)函數(shù)名稱在數(shù)據(jù)庫(kù)內(nèi)必須唯一，并且符合標(biāo)識(shí)符的規(guī)則。
input_parameter_type
是用于分區(qū)的列的數(shù)據(jù)類型。當(dāng)用作分區(qū)列時(shí)，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名數(shù)據(jù)類型或 CLR 用戶定義數(shù)據(jù)類型外，所有數(shù)據(jù)類型均有效。
boundary_value 為 使用 partition_function_name 的已分區(qū)表或索引的每個(gè)分區(qū)指定邊界值。如果 boundary_value 為空，則分區(qū)函數(shù)使用 partition_function_name 將整個(gè)表或索引映射到單個(gè)分區(qū)。只能使用 CREATE TABLE 或 CREATE INDEX 語(yǔ)句中指定的一個(gè)分區(qū)列。boundary_value 是可以引用變量的常量表達(dá)式。這包括用戶定義類型變量，或函數(shù)以及用戶定義函數(shù)。它不能引用 Transact-SQL 表達(dá)式。boundary_value 必須與 input_parameter_type 中提供的數(shù)據(jù)類型相匹配或者可隱式轉(zhuǎn)換為該數(shù)據(jù)類型，并且如果該值的大小和小數(shù)位數(shù)與 input_parameter_type 中相應(yīng)的值的大小和小數(shù)位數(shù)不匹配，則在隱式轉(zhuǎn)換過(guò)程中該值不能被截?cái)唷?/p>
注意：
如果 boundary_value 包含
datetime 或 smalldatetime 文字值，則為這些文字值在計(jì)算時(shí)假設(shè) us_english
是會(huì)話語(yǔ)言。不推薦使用此行為。要確保分區(qū)函數(shù)定義對(duì)于所有會(huì)話語(yǔ)言都具有預(yù)期的行為，建議使用對(duì)于所有語(yǔ)言設(shè)置都以相同方式進(jìn)行解釋的常量，例如
yyyymmdd 格式；或者將文字值顯式轉(zhuǎn)換為特定樣式。有關(guān)詳細(xì)信息，請(qǐng)參閱編寫(xiě)國(guó)際化 Transact-SQL
語(yǔ)句。若要確定服務(wù)器的語(yǔ)言會(huì)話，請(qǐng)運(yùn)行 SELECT @@LANGUAGE。
指定
boundary_value 提供的值的數(shù)目，不能超過(guò) 999。所創(chuàng)建的分區(qū)數(shù)等于 n + 1。不必按順序列出各值。如果值未按順序列出，則
Microsoft SQL Server 2005 數(shù)據(jù)庫(kù)引擎 將對(duì)它們進(jìn)行排序，創(chuàng)建函數(shù)并返回一個(gè)警告，說(shuō)明未按順序提供值。如果 n
包括任何重復(fù)的值，則數(shù)據(jù)庫(kù)引擎將返回錯(cuò)誤。
LEFT | RIGHT
指定當(dāng)間隔值由 數(shù)據(jù)庫(kù)引擎 按升序從左到右排序時(shí)，boundary_value [ ,...n ] 屬于每個(gè)邊界值間隔的哪一側(cè)（左側(cè)還是右側(cè)）。如果未指定，則默認(rèn)值為 LEFT。
創(chuàng)建分區(qū)函數(shù)示例
CREATE PARTITION FUNCTION PF_Left(int) AS RANGE LEFT FOR VALUES(10, 20) GO CREATE PARTITION FUNCTION PF_Right(int) AS RANGE LEFT FOR VALUES(10, 20) GO
PF_Left 和 PF_Right 分區(qū)函數(shù)的區(qū)分：
分區(qū)函數(shù) 分區(qū)1 分區(qū)2 分區(qū)3
PF_Left <= 10 > 10 and <= 20 > 20
PF_Right < 10 >= 10 and < 20 >= 20
4. 創(chuàng)建分區(qū)架構(gòu)（Schema）
創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)架構(gòu)（Schema）相關(guān)聯(lián)，以便將分區(qū)定向至特定的文件組。定義分區(qū)架構(gòu)師，即使多個(gè) 分區(qū)位于同一個(gè)文件組中，也必須為每個(gè)分區(qū)指定一個(gè)文件組。
創(chuàng)建分區(qū)架構(gòu)的語(yǔ)法如下：
GOCREATE PARTITION SCHEME partition_scheme_name AS PARTITION partition_function_name [ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
參數(shù)：
partition_scheme_name
分區(qū)方案的名稱。分區(qū)方案名稱在數(shù)據(jù)庫(kù)中必須是唯一的，并且符合標(biāo)識(shí)符規(guī)則。
partition_function_name
使用分區(qū)方案的分區(qū)函數(shù)的名稱。分區(qū)函數(shù)所創(chuàng)建的分區(qū)將映射到在分區(qū)方案中指定的文件組。partition_function_name 必須已經(jīng)存在于數(shù)據(jù)庫(kù)中。
ALL
指定所有分區(qū)都映射到在 file_group_name 中提供的文件組，或映射到主文件組（如果指定了 [PRIMARY]。如果指定了 ALL，則只能指定一個(gè) file_group_name。
file_group_name | [ PRIMARY ] [ ,...n]
指定用來(lái)持有由 partition_function_name 指定的分區(qū)的文件組的名稱。file_group_name 必須已經(jīng)存在于數(shù)據(jù)庫(kù)中。
如
果指定了 [PRIMARY]，則分區(qū)將存儲(chǔ)于主文件組中。如果指定了 ALL，則只能指定一個(gè)
file_group_name。分區(qū)分配到文件組的順序是從分區(qū) 1 開(kāi)始，按文件組在 [,...n] 中列出的順序進(jìn)行分配。在 [,...n]
中，可以多次指定同一個(gè) file_group_name。如果 n 不足以擁有在 partition_function_name
中指定的分區(qū)數(shù)，則 CREATE PARTITION SCHEME 將失敗，并返回錯(cuò)誤。
如果
partition_function_name 生成的分區(qū)數(shù)少于文件組數(shù)，則第一個(gè)未分配的文件組將標(biāo)記為 NEXT USED，并且出現(xiàn)顯示命名
NEXT USED 文件組的信息。如果指定了 ALL，則單獨(dú)的 file_group_name 將為該
partition_function_name 保持它的 NEXT USED 屬性。如果在 ALTER PARTITION FUNCTION
語(yǔ)句中創(chuàng)建了一個(gè)分區(qū)，則 NEXT USED 文件組將再接收一個(gè)分區(qū)。若要再創(chuàng)建一個(gè)未分配的文件組來(lái)?yè)碛行碌姆謪^(qū)，請(qǐng)使用 ALTER
PARTITION SCHEME。
在 file_group_name[ 1,...n] 中指定主文件組時(shí)，必須像在 [PRIMARY] 中那樣分隔 PRIMARY，因?yàn)樗顷P(guān)鍵字。
創(chuàng)建分區(qū)架構(gòu)示例：
--創(chuàng)建分區(qū)函數(shù) CREATE PARTITION FUNCTION FS_Range (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO --創(chuàng)建分區(qū)方案 CREATE PARTITION SCHEME PS_Range AS PARTITION FS_Range TO (test1fg, test2fg, test3fg, test4fg); GO
5. 創(chuàng)建分區(qū)表
定義了分區(qū)函數(shù)（邏輯結(jié)構(gòu)）和 分區(qū)架構(gòu)（物理結(jié)構(gòu)）后，既可以創(chuàng)建分區(qū)表來(lái)利用它們。分區(qū)表定義應(yīng)使用的分區(qū)架構(gòu)，而分區(qū)架構(gòu)又定義其使用的分區(qū)函數(shù)。要將這三者結(jié)合起來(lái)，必須指定應(yīng)用于分區(qū)函數(shù)的 列 。范圍分區(qū)始終只映射到表中的一列。
CREATE TABLE 語(yǔ)法如下：
CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name ( { <column_definition> | <computed_column_definition> } [ <table_constraint> ] [ ,...n ] ) [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] [ { TEXTIMAGE_ON { filegroup | "default" } ]
示例如下：
CREATE TABLE Test ( ID int not null, AGE int, PRIMARY KEY (ID) ) ON PS_Range(ID) GO
6. 創(chuàng)建分區(qū)索引
索引對(duì)于提高查詢性能非常有效，因此，一般應(yīng)該考慮應(yīng)該考慮為分區(qū)表建立索引，為分區(qū)表建立索引與為普通表建立索引的語(yǔ)法一直，但是，其行為與普通索引有所差異。
默認(rèn)情況下，分區(qū)表中創(chuàng)建的索引使用與分區(qū)表相同分區(qū)架構(gòu)和分區(qū)列，這樣，索引將于表對(duì)齊。將表與其索引對(duì)齊，可以使管理工作更容易進(jìn)行，對(duì)于滑動(dòng)窗口方案尤其如此。若要啟動(dòng)分區(qū)切換，表的所有索引都必須對(duì)齊。
在創(chuàng)建索引時(shí)，也可以指定不同的分區(qū)方案（Schema）或單獨(dú)的文件組（FileGroup）來(lái)存儲(chǔ)索引，這樣SQL Server 不會(huì)將索引與表對(duì)齊。
在已分區(qū)的表上創(chuàng)建索引（分區(qū)索引）時(shí)，應(yīng)該注意以下事項(xiàng)：
唯一索引
建立唯一索引（聚集或者非聚集）時(shí)，分區(qū)列必須出現(xiàn)在索引列中。此限制將使SQL Server只調(diào)查單個(gè)分區(qū)，并確保表中 寵物的新鍵值。如果分區(qū)依據(jù)列不可能包含在唯一鍵中，則必須使用DML觸發(fā)器，而不是強(qiáng)制實(shí)現(xiàn)唯一性。
非唯一索引
對(duì)非唯一的聚集索引進(jìn)行分區(qū)時(shí)，如果未在聚集鍵中明確指定分區(qū)依據(jù)列，默認(rèn)情況下SQL Server 將在聚集索引列中添加分區(qū)依據(jù)列。
對(duì)非唯一的非聚集索引進(jìn)行分區(qū)時(shí)，默認(rèn)情況下SQL Server 將分區(qū)依據(jù)列添加為索引的包含性列，以確保索引與基表對(duì)齊，若果索引中已經(jīng)存在分區(qū)依據(jù)列，SQL Server 將不會(huì)像索引中添加分區(qū)依據(jù)列。
四、分區(qū)操作
分區(qū)適用于可以縮放的大型表，所以隨著時(shí)間和環(huán)境的變化，就會(huì)產(chǎn)生對(duì)分區(qū)的拆分、合并、移動(dòng)的需求。
1. 拆分與合并分區(qū)
通過(guò)拆分或合并邊界值更改分區(qū)函數(shù)。通過(guò)執(zhí)行 ALTER PARTITION FUNCTION，可以將使用分區(qū)函數(shù)的任何表或索引的某個(gè)分區(qū)拆分為兩個(gè)分區(qū)，也可以將兩個(gè)分區(qū)合并為一個(gè)分區(qū)。
注意：多個(gè)表或索引可以使用同一分區(qū)函數(shù)。ALTER PARTITION FUNCTION 在單個(gè)事務(wù)中影響所有這些表或索引。
ALTER PARTITION FUNCTION 語(yǔ)法如下：
ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) }
參數(shù)說(shuō)明：
partition_function_name
要修改的分區(qū)函數(shù)的名稱。
SPLIT RANGE ( boundary_value )
在
分區(qū)函數(shù)中添加一個(gè)分區(qū)。boundary_value 確定新分區(qū)的范圍，因此它必須不同于分區(qū)函數(shù)的現(xiàn)有邊界范圍。根據(jù)
boundary_value，Microsoft SQL Server 2005 數(shù)據(jù)庫(kù)引擎 將某個(gè)現(xiàn)有范圍拆分為兩個(gè)范圍。在這兩個(gè)范圍中，新
boundary_value 所在的范圍被視為是新分區(qū)。
重要提示：
文件組必須處于聯(lián)機(jī)狀態(tài)，并且必須由使用此分區(qū)函數(shù)的分區(qū)
方案標(biāo)記為 NEXT USED，以保存新分區(qū)。在 CREATE PARTITION SCHEME 語(yǔ)句中，將把文件組分配給分區(qū)。如果
CREATE PARTITION SCHEME 語(yǔ)句分配了多余的文件組（在 CREATE PARTITION FUNCTION
語(yǔ)句中創(chuàng)建的分區(qū)數(shù)少于用于保存它們的文件組），則存在未分配的文件組，分區(qū)方案將把其中的某個(gè)文件組標(biāo)記為 NEXT
USED。該文件組將保存新的分區(qū)。如果分區(qū)方案未將任何文件組標(biāo)記為 NEXT USED，則必須使用 ALTER PARTITION SCHEME
添加一個(gè)文件組或指定一個(gè)現(xiàn)有文件組來(lái)保存新分區(qū)。可以指定已保存分區(qū)的文件組來(lái)保存附加分區(qū)。由于一個(gè)分區(qū)函數(shù)可以參與多個(gè)分區(qū)方案，因此所有使用分區(qū)
函數(shù)（您向其中添加了分區(qū)）的分區(qū)方案都必須擁有一個(gè) NEXT USED 文件組。否則，ALTER PARTITION FUNCTION
將失敗并出現(xiàn)錯(cuò)誤，該錯(cuò)誤顯示缺少 NEXT USED 文件組的一個(gè)或多個(gè)分區(qū)方案。
MERGE [ RANGE ( boundary_value) ]
刪
除一個(gè)分區(qū)并將該分區(qū)中存在的所有值都合并到剩余的某個(gè)分區(qū)中。RANGE (boundary_value)
必須是一個(gè)現(xiàn)有邊界值，已刪除分區(qū)中的值將合并到該值中。如果最初保存 boundary_value 的文件組沒(méi)有被剩余分區(qū)使用，也沒(méi)有使用
NEXT USED 屬性進(jìn)行標(biāo)記，則將從分區(qū)方案中刪除該文件組。合并的分區(qū)駐留在最初不保存 boundary_value
的文件組中。boundary_value 是一個(gè)可以引用變量（包括用戶定義類型變量）或函數(shù)（包括用戶定義函數(shù)）的常量表達(dá)式。它無(wú)法引用
Transact-SQL 表達(dá)式。boundary_value 必須匹配或可以隱式轉(zhuǎn)換為其對(duì)應(yīng)列的數(shù)據(jù)類型，并且當(dāng)值的大小和小數(shù)位數(shù)不匹配其對(duì)應(yīng)
input_parameter_type 時(shí)，將無(wú)法在隱式轉(zhuǎn)換過(guò)程中被截?cái)唷?br>
示例：
ALTER PARTITION SCHEME PS_HistoryArchive NEXT USED [PRIMARY]
備注：
ALTER PARTITION FUNCTION 在單個(gè)原子操作中對(duì)使用該函數(shù)的任何表和索引進(jìn)行重新分區(qū)。但該操作在脫機(jī)狀態(tài)下進(jìn)行，并且根據(jù)重新分區(qū)的范圍，可能會(huì)消耗大量資源。
ALTER
PARTITION FUNCTION
只能用于將一個(gè)分區(qū)拆分為兩個(gè)分區(qū)，或?qū)蓚€(gè)分區(qū)合并為一個(gè)分區(qū)。若要更改其他情況下對(duì)表進(jìn)行分區(qū)方法（例如，將 10 個(gè)分區(qū)合并為 5
個(gè)分區(qū)），可以嘗試使用以下任何選項(xiàng)。根據(jù)系統(tǒng)配置，這些選項(xiàng)可能在資源消耗方面有所不同：
使用所需的分區(qū)函數(shù)創(chuàng)建一個(gè)新的已分區(qū)表，然后使用 INSERT INTO...SELECT FROM 語(yǔ)句將舊表中的數(shù)據(jù)插入新表。
為堆創(chuàng)建分區(qū)聚集索引。
注意：
刪除已分區(qū)的聚集索引將產(chǎn)生分區(qū)堆。
通過(guò)將 Transact-SQL CREATE INDEX 語(yǔ)句與 DROP EXISTING = ON 子句一起使用來(lái)刪除并重新生成現(xiàn)有的已分區(qū)索引。
執(zhí)行一系列 ALTER PARTITION FUNCTION 語(yǔ)句。
ALTER PARITITION FUNCTION 所影響的全部文件組都必須處于聯(lián)機(jī)狀態(tài)。
如果使用分區(qū)函數(shù)的任何表中存在已禁用的聚集索引，ALTER PARTITION FUNCTION 都將失敗。
Microsoft SQL Server 2005 不對(duì)修改分區(qū)函數(shù)提供復(fù)制支持。必須在訂閱數(shù)據(jù)庫(kù)中手動(dòng)應(yīng)用對(duì)發(fā)布數(shù)據(jù)庫(kù)中的分區(qū)函數(shù)的更改。
2. 移動(dòng)分區(qū)數(shù)據(jù)
可以使用 ALTER TABLE ....... SWITCH 語(yǔ)句按一下方式快速有效地移動(dòng)數(shù)據(jù)子集：
將某個(gè)表中的數(shù)據(jù)移動(dòng)到另一個(gè)表中；
將某個(gè)表作為分區(qū)添加到現(xiàn)存的已分區(qū)表中；
將分區(qū)從一個(gè)已分區(qū)表切換到另一個(gè)已分區(qū)表；
刪除分區(qū)以形成單個(gè)表。
使用這些方案移動(dòng)數(shù)據(jù)時(shí)，無(wú)論集合有多大，此方案都能快速有效地進(jìn)行傳輸，因?yàn)椴僮鞑⒉灰晕锢矸绞揭苿?dòng)數(shù)據(jù)，只有關(guān)于存儲(chǔ)位置的元數(shù)據(jù)會(huì)從一個(gè)分區(qū)變?yōu)榱硪粋€(gè)分區(qū)。
ALTER TABLE .... SWITCH 的語(yǔ)法如下：
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { SWITCH [ PARTITION source_partition_number_expression ] TO [schema_name].target_table [ PARTITION target_partition_number_expression ] }
五、使用腳本快速創(chuàng)建表分區(qū)
假設(shè)有一個(gè)數(shù)據(jù)庫(kù)LogDB
USE LogDB --創(chuàng)建表 CREATE TABLE [dbo].[ErrLog]( [ErrID] [bigint] IDENTITY(1,1) PRIMARY KEY NOT NULL, [TypeID] [smallint] NOT NULL, [Message] [varchar](max) NULL, [TimeCreate] [datetime] NULL, [ErrUrl] [varchar](200) NULL, [ErrUser] [bigint] NULL, [ErrIP] [bigint] NULL) --創(chuàng)建分區(qū)函數(shù) CREATE PARTITION FUNCTION [M_FS_CreateTime](datetime) AS RANGE LEFT FOR VALUES (N'2013-05-01T00:00:00.000', N'2013-05-02T00:00:00.000', N'2013-05-03T00:00:00.000') GO --創(chuàng)建分區(qū)方案 /****** Object: PartitionScheme [M_PS_CreateTime] Script Date: 05/09/2013 17:10:41 ******/ CREATE PARTITION SCHEME [M_PS_CreateTime] AS PARTITION [M_FS_CreateTime] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) GO --創(chuàng)建分區(qū)索引 BEGIN TRANSACTION CREATE CLUSTERED INDEX [ClusteredIndex_on_M_PS_CreateTime_ErrLog] ON [dbo].[ErrLog] ( [TimeCreate] ASC )WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [M_PS_CreateTime]([TimeCreate]) COMMIT TRANSACTION
GO
浙公網(wǎng)安備 33010602011771號(hào)