SQL Server移动带聚集主键的表至文件组：磁盘占用翻倍问题咨询

问题场景 #

有一张包含聚集主键的表，需要移动到对应独立文件的文件组，当前采用删除再重建聚集主键约束的方式，出现磁盘占用翻倍的情况，执行的SQL代码如下：

USE [database]
GO

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [PK_table_name] WITH ( ONLINE = OFF )
GO

ALTER TABLE [dbo].[table_name] ADD  CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED 
(
    [EquipId] ASC,
    [PollTimeUtc] ASC,
    [EquipTypeParamId] ASC,
    [ChannelNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [table_name]
GO

CONSTRAINT与INDEX在磁盘占用和表移动场景的区别 #

• 约束（CONSTRAINT）的核心逻辑：聚集主键约束是逻辑规则，它必须依赖聚集索引实现物理存储。通过ALTER TABLE ADD CONSTRAINT创建聚集主键时，SQL Server会先验证数据符合主键规则（无重复、非空），再构建索引结构——过程中会生成临时验证数据，加上原表数据未被即时清理，极易导致磁盘占用翻倍。
• 索引（INDEX）的物理特性：直接操作聚集索引（如重建）是针对物理存储结构的调整，SQL Server可以更高效地利用空间。离线重建时，甚至可以直接覆盖原结构；若开启SORT_IN_TEMPDB，排序的临时空间会转移到tempdb，进一步减少目标文件组的临时占用。
• 核心差异：约束操作优先保证逻辑规则的合法性，额外开销大；索引操作直接处理物理存储，在文件组移动场景下空间利用率更高。

是否必须删除再重建聚集主键？ #

不需要，有两种更高效的方案：

1. 直接重建聚集索引（保留主键约束）
   聚集主键对应的索引可直接重建到目标文件组，无需删除约束：

   USE [database]
   GO
   ALTER INDEX [PK_table_name] ON [dbo].[table_name] REBUILD WITH (
       ONLINE = OFF,
       PAD_INDEX = OFF,
       STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = ON,
       IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON,
       OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
   ) ON [table_name]; -- 指定目标文件组
   GO
   

   该操作会直接将整个表（聚集索引即表本身）移动到目标文件组，过程中不会产生两倍于原表的磁盘占用。

2. 使用CREATE INDEX ... WITH DROP_EXISTING
   若需调整索引属性（主键键列不可修改），可通过此语法替换现有聚集索引，同时保留主键约束：

   USE [database]
   GO
   CREATE CLUSTERED INDEX [PK_table_name] ON [dbo].[table_name]
   (
       [EquipId] ASC,
       [PollTimeUtc] ASC,
       [EquipTypeParamId] ASC,
       [ChannelNumber] ASC
   ) WITH (
       DROP_EXISTING = ON,
       ONLINE = OFF,
       PAD_INDEX = OFF,
       STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = ON,
       IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON,
       OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
   ) ON [table_name];
   GO
   

原方案磁盘占用翻倍的原因 #

删除聚集主键约束后，表会变成堆表，原数据仍留在原文件组；添加聚集主键约束时，SQL Server需要基于堆表在目标文件组重建聚集索引，此时磁盘上同时存在堆表数据和新的聚集索引数据，直到操作完成后才会删除堆表，因此过程中磁盘占用会达到原表的两倍左右。

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