IT工程師數位筆記本

在網上能夠找到很多關于表分區的資料，可是大部分都是在介紹如何給一個新表創建表分區，而對已存在的表如何做分區的文章相對比較少，因此一些坑沒有被“挖掘”出來或者“曝光率”比較低。

筆者最近遇到了一個這樣的案例，剛好踩到了兩個坑，現分享給大家。

對已存在的表進行分區最常見的方法就是重建聚集索引或者創建聚集索引（如果表上沒有）。因為聚集索引的頁級就是實際數據，而重建或者創建聚集索引時會重新組織頁，所以如果在重建或者創建索引時指定分區架構，那么該表上所有的數據就會按照分區架構進行表分區。

在使用此法對表進行分區，有兩個坑請大家注意：

坑一：重建索引時，提示“新的索引定義與現有的索引強制的約束不匹配”

請看如下代碼：

CREATE clustered index

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTING

ON [PS_Sod](ModifiedDate)

參數with drop_existing表示給定的索引作為一個事務被刪除和重建；

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID是索引的名稱，它建立在表Sales.SalesOrderDetail的SalesOrderID, SalesOrderDetailID字段上；

PS_Sod為分區架構，其參數ModifiedDate表示分區列；

當我執行時，提示錯誤如下：

消息1907，級別16，狀態1，第2 行

無法重新重建索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'。新的新的索引定義與現有的索引強制的約束不匹配.

從報錯信息來看，新創建的索引與原索引的約束不匹配，于是執行如下語句，查看當前表身上的約束信息：

SELECT CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE CONSTRAINT_NAME = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'

返回的結果為：primary

也就是說，當前表上的(SalesOrderID, SalesOrderDetailID)存在主鍵約束，從而確保(SalesOrderID, SalesOrderDetailID)的唯一性，而重建索引時，沒有指定唯一性，因此需要改成如下語句：

CREATE UNIQUE clustered index

PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTING

ON [PS_Sod](ModifiedDate)

坑二：”唯一索引的分區依據列必須是索引鍵的子集”

即使上述方法修改了執行語句，還是會報錯，只是報錯的內容不再是約束有關，而是唯一索引的分區依據列必須是索引鍵的子集。

消息1908，級別16，狀態1，第2 行

列'ModifiedDate' 是索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID' 的分區依據列。唯一索引的分區依據列必須是索引鍵的子集。

也就是說，如果要在唯一索引重建時分區，必須要求分區的依據列是唯一索引的一部分。

如此看來，要想通過重建聚集索引的方式對表進行分區，需要滿足如下2個條件中的任意一個：

1. 將分區列添加到索引中；

     不過，很多時候分區依據列是datetime類型的字段，作為索引的可選擇性不強，將其添加到索引中并不滿足索引創建的最佳實踐。

2. 索引沒有被顯示標記為unique且不存在主鍵約束；

    這樣就不存在唯一索引的說法，也就不需要分區的依據列是唯一索引的一部分；

    不過，在實際場景中，如果表的索引在創建時被顯示指定為unique了，還是有辦法通過索引“重建”進行分區的，只不過不能使用在線索引重建的方法，我們必須先手動刪除索引，然后再來創建非unique的索引。這種方式相比with drop_existing重建索引性能要差很多，因為刪除一個聚集索引時，SQL Server必須重建每一個非聚集索引（假設存在非聚集索引），從而將其書簽修改為RID，然后，在創建聚集索引時，又需要將每個非聚集索引的RID更改書簽。而with drop_existing只需要重建一次非聚集索引，當然，如果你在完全相同的鍵上建立索引，非聚集索引根本不需要重建。

   至于主鍵約束的限制，相比較而言就比較簡單了，即使表中存在主鍵約束，刪除它時也不會存在性能上的影響。