如何缩小SQL Server中存储长文本的表的磁盘占用空间？

你可以通过修改列类型缩小表占用空间，也可以选择侵入性更低的其他优化方案，所有方案都可在不丢失数据的前提下完成。

首先确认你的场景是否适合修改列类型：nvarchar(n) 最大支持存储4000个字符，如果你所有列的实际存储内容长度都不超过4000，修改为固定长度的nvarchar类型可以有效减少空间占用。

1. 先统计每个大字段的实际最大字符长度，执行以下SQL：

-- 替换为你的实际列名、表名
SELECT MAX(DATALENGTH(你的列名)/2) AS 最大字符数 FROM 你的表名;

2. 如果返回的最大字符数≤4000，选择比最大字符数略大的n值（比如最大字符数是2000，就设为nvarchar(2500)），执行修改语句：

ALTER TABLE 你的表名 ALTER COLUMN 你的列名 nvarchar(2500) NOT NULL;
-- 注意根据实际情况调整是否允许为NULL

3. 修改完成后回收闲置空间：

-- 回收列类型变更产生的未使用空间
DBCC CLEANTABLE (你的数据库名, '你的表名');
-- 重建所有索引释放碎片空间
ALTER INDEX ALL ON 你的表名 REBUILD;

如果你的字段实际存储字符数超过4000，无法修改为非MAX的nvarchar类型，可以选择以下方案优化：

• 启用SQL Server数据压缩：SQL Server 2016及以上版本支持LOB数据压缩，对文本类内容的压缩率通常可达30%~70%，无需修改表结构，侵入性极低，执行语句如下：

ALTER TABLE 你的表名 REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE, LOB_COMPRESSION = ON);

• 冗余数据拆分：如果存在大量重复存储的大文本内容，可新增独立的文本内容表存储去重后的文本，原表只存储关联的内容ID，可大幅降低空间占用。
• 清理冗余索引：排查所有非聚集索引，删除包含大文本列的无用索引，有用的索引调整包含列规则，排除不需要的大文本字段，减少索引额外占用的空间。

• 所有优化操作前必须完成全量数据备份，确认备份可用后再执行操作
• 大表的优化操作会占用大量IO、CPU资源，且会锁表影响业务，必须在业务低峰期执行，操作前需在测试环境完成全流程模拟验证
• 优化完成后可执行sp_spaceused '你的表名'对比优化前后的空间占用情况

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