在網(wǎng)上能夠找到很多關(guān)于表分區(qū)的資料，可是大部分都是在介紹如何給一個(gè)新表創(chuàng)建表分區(qū)，而對(duì)已存在的表如何做分區(qū)的文章相對(duì)比較少，因此一些坑沒有被“挖掘”出來(lái)或者“曝光率”比較低。

筆者最近遇到了一個(gè)這樣的案例，剛好踩到了兩個(gè)坑，現(xiàn)分享給大家。

對(duì)已存在的表進(jìn)行分區(qū)最常見的方法就是重建聚集索引或者創(chuàng)建聚集索引（如果表上沒有）。因?yàn)榫奂饕捻?yè)級(jí)就是實(shí)際數(shù)據(jù)，而重建或者創(chuàng)建聚集索引時(shí)會(huì)重新組織頁(yè)，所以如果在重建或者創(chuàng)建索引時(shí)指定分區(qū)架構(gòu)，那么該表上所有的數(shù)據(jù)就會(huì)按照分區(qū)架構(gòu)進(jìn)行表分區(qū)。

在使用此法對(duì)表進(jìn)行分區(qū)，有兩個(gè)坑請(qǐng)大家注意：

坑一：重建索引時(shí)，提示“新的索引定義與現(xiàn)有的索引強(qiáng)制的約束不匹配”

請(qǐng)看如下代碼：

CREATE clustered indexPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTINGON [PS_Sod](ModifiedDate)

參數(shù)with drop_existing表示給定的索引作為一個(gè)事務(wù)被刪除和重建；

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

PS_Sod為分區(qū)架構(gòu)，其參數(shù)ModifiedDate表示分區(qū)列；

當(dāng)我執(zhí)行時(shí)，提示錯(cuò)誤如下：

消息1907，級(jí)別16，狀態(tài)1，第2 行

無(wú)法重新重建索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'。新的新的索引定義與現(xiàn)有的索引強(qiáng)制的約束不匹配.

從報(bào)錯(cuò)信息來(lái)看，新創(chuàng)建的索引與原索引的約束不匹配，于是執(zhí)行如下語(yǔ)句，查看當(dāng)前表身上的約束信息：

SELECT CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE CONSTRAINT_NAME = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'

返回的結(jié)果為：PRimary

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

CREATE UNIQUE clustered indexPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID ON Sales.SalesOrderDetail(SalesOrderID, SalesOrderDetailID) WITH DROP_EXISTINGON [PS_Sod](ModifiedDate)

坑二：”唯一索引的分區(qū)依據(jù)列必須是索引鍵的子集”

即使上述方法修改了執(zhí)行語(yǔ)句，還是會(huì)報(bào)錯(cuò)，只是報(bào)錯(cuò)的內(nèi)容不再是約束有關(guān)，而是唯一索引的分區(qū)依據(jù)列必須是索引鍵的子集。

消息1908，級(jí)別16，狀態(tài)1，第2 行

列'ModifiedDate' 是索引'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID' 的分區(qū)依據(jù)列。唯一索引的分區(qū)依據(jù)列必須是索引鍵的子集。

也就是說，如果要在唯一索引重建時(shí)分區(qū)，必須要求分區(qū)的依據(jù)列是唯一索引的一部分。

如此看來(lái)，要想通過重建聚集索引的方式對(duì)表進(jìn)行分區(qū)，需要滿足如下2個(gè)條件中的任意一個(gè)：

1. 將分區(qū)列添加到索引中；

不過，很多時(shí)候分區(qū)依據(jù)列是datetime類型的字段，作為索引的可選擇性不強(qiáng)，將其添加到索引中并不滿足索引創(chuàng)建的最佳實(shí)踐。

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

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

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

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