对已存在的表进行分区时遇到的坑

 

在网上能够找到很多关于表分区的资料，可是大部分都是在介绍如何给一个新表创建表分区，而对已存在的表如何做分区的文章相对比较少，因此一些坑没有被“挖掘”出来或者“曝光率”比较低。

笔者最近遇到了一个这样的案例，刚好踩到了两个坑，现分享给大家。

对已存在的表进行分区最常见的方法就是重建聚集索引或者创建聚集索引（如果表上没有）。因为聚集索引的页级就是实际数据，而重建或者创建聚集索引时会重新组织页，所以如果在重建或者创建索引时指定分区架构，那么该表上所有的数据就会按照分区架构进行表分区。

在使用此法对表进行分区，有两个坑请大家注意：

 

坑一：重建索引时，提示“新的索引定义与现有的索引强制的约束不匹配”

请看如下代码：

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只需要重建一次非聚集索引，当然，如果你在完全相同的键上建立索引，非聚集索引根本不需要重建。

   至于主键约束的限制，相比较而言就比较简单了，即使表中存在主键约束，删除它时也不会存在性能上的影响。

转载于:https://www.cnblogs.com/i6first/p/4531685.html