SQL Server拆分分区报错、无法删除分区方案问题咨询

• 拆分250时报「未设置任何下一个使用的文件组」的原因：创建分区方案时使用ALL TO PRIMARY语法，仅会将创建时刻已存在的全部分区映射到PRIMARY文件组，不会自动为PRIMARY标记NEXT USED属性。第一次拆分200成功，是因为拆分点落在已绑定PRIMARY的现有分区区间内，拆分生成的两个子分区可直接复用原分区的文件组映射，无需调用预留的下一个可用文件组；第一次拆分完成后，分区方案没有剩余的可自动分配的文件组配额，第二次执行拆分时就会触发报错。
• 重建聚簇索引后仍无法删除分区方案的原因：仅迁移了聚簇索引的存储位置，表上的非聚簇索引（包括唯一约束、主键对应的非聚簇索引）仍然绑定在原分区方案上。SQL Server中只要有任何从属对象引用分区方案，就会锁定分区方案不允许删除，同时元数据层面会标记该表为分区方案、分区函数的依赖项，和你在SSMS中看到的现象一致。

清理残留依赖，删除无用分区对象 #

1. 先查询目标表上所有索引的实际存储位置，确认残留绑定分区方案的索引，执行如下T-SQL：

SELECT 
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ds.name AS DataSpaceName,
    ds.type_desc AS DataSpaceType
FROM sys.indexes i
INNER JOIN sys.data_spaces ds 
    ON i.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('dbo.PartitionTable1')
AND i.index_id > 0

执行结果中，DataSpaceName显示为PartFuncScheme的就是未迁移的残留索引。
2. 对所有残留的非聚簇索引，逐个执行重建操作，将其迁移到PRIMARY文件组，重建时带上DROP_EXISTING = ON选项减少不必要的开销，语法模板如下：

CREATE NONCLUSTERED INDEX [替换为实际索引名]
ON [dbo].[PartitionTable1]([替换为索引对应的列列表])
WITH (DROP_EXISTING = ON)
ON [PRIMARY]

如果是主键、唯一约束对应的索引，直接用对应约束的索引定义重建即可，不需要先删除约束。
3. 所有索引迁移完成后，再次执行第一步的查询，确认没有任何索引绑定在PartFuncScheme上，即可按顺序删除分区方案、分区函数：

DROP PARTITION SCHEME PartFuncScheme;
DROP PARTITION FUNCTION PartFunc;

后续分区拆分的正确操作 #

如果后续仍需要使用分区功能，每次执行SPLIT RANGE操作前，必须显式为分区方案指定下一个使用的文件组，哪怕所有分区都放在PRIMARY文件组上也需要显式声明，语法如下：

-- 先指定下一个可用文件组
ALTER PARTITION SCHEME PartFuncScheme NEXT USED [PRIMARY];
-- 再执行拆分操作
ALTER PARTITION FUNCTION PartFunc() SPLIT RANGE(250);

注意：300万行的表分区拆分时会涉及数据移动，建议在业务低峰期操作，避免锁表影响业务。

内容的提问来源于stack exchange，提问作者Morley Denesis