将nvarchar(max)列改为nvarchar(15)后数据库文件占用空间增加？

我在SQL Server 2019中有一张大表，包含约75,172,085行数据，总大小约33GB，其中有10个nvarchar(max)类型的文本列。

我在测试环境中执行了以下语句，将其中一个原本可空、未参与任何索引或全文索引的nvarchar(max)列修改为nvarchar(15)：

ALTER TABLE [dbo].[TableName] ALTER COLUMN [ColumnName] NVARCHAR (15) NULL;

修改前空间统计 #

表空间（sp_spaceused结果） #

数据库数据文件（dbo.sysfiles结果） #

修改后空间统计 #

表空间（sp_spaceused结果） #

数据库数据文件（dbo.sysfiles结果） #

可以看到，磁盘上的数据文件总大小没有增长，但文件内部已使用空间增加了约23GB，表的数据空间也同步增长了约23GB（单位转换可能存在微小误差）。

我想了解这个现象的原因，以及有没有方法释放这些被占用的空间？

原因分析 #

SQL Server在修改nvarchar(max)列的类型时，不会直接原地更新原有数据，而是采用行版本化的方式处理：

• nvarchar(max)类型的数据默认存储在LOB（大对象）页中，而nvarchar(15)属于常规行内存储类型。
• 执行ALTER COLUMN时，SQL Server会为每一行生成新的行记录，将原LOB页中的数据迁移到行内，同时原LOB页并不会立即被标记为可重用，暂时占用着空间。
• 此外，ALTER TABLE操作会产生大量事务日志，过程中需要额外空间存储临时数据，这些临时空间在操作完成后不会自动回收，导致表的已保留空间和数据文件已使用空间大幅增加。

释放空间的方法 #

1. 重建聚集索引（若表存在聚集索引） #

如果表有聚集索引，重建操作会重新组织表数据，回收未使用的LOB页空间：

ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD WITH (ONLINE = ON); -- 在线重建避免锁表，需企业版支持

若无聚集索引，可先创建临时聚集索引再删除，或使用下方方法。

2. 重建整个表 #

直接重建表，强制SQL Server重新组织所有数据页，回收空闲空间：

ALTER TABLE [dbo].[TableName] REBUILD;

3. 收缩数据文件（谨慎使用） #

若数据库数据文件存在大量未使用空间，可收缩文件，但此操作会产生索引碎片，建议在重建索引后执行：

DBCC SHRINKFILE (N'YourDataFileName', TRUNCATEONLY); -- TRUNCATEONLY仅释放文件末尾空闲空间，不移动数据

注意：SHRINKFILE不建议频繁使用，会加剧索引碎片、降低查询性能，仅在确认有大量空闲空间且后续不会快速增长时使用。

4. 更新空间统计 #

操作完成后，更新表的空间统计，确保sp_spaceused显示准确：

UPDATE STATISTICS [dbo].[TableName] WITH FULLSCAN;
EXEC sp_spaceused '[dbo].[TableName]';

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