SQL Server 2012中varchar(max)移至新表后，新表占用空间更大原因排查

这确实是个有点反直觉的问题，我来帮你梳理几个容易忽略的关键点，看看为什么会出现这种情况：

• 数据页填充率差异
  原表中有大量行的varchar(max)字段为NULL，这些行的整体长度比较小，每个数据页可以容纳更多行，填充率很高。而新表只包含有数据的行，每一行都带有非NULL的varchar(max)数据，行的实际长度更大，导致每个数据页能容纳的行数大幅减少。哪怕新表只有原表75%的行数，总数据页数可能反而更多，最终占用的空间也就更大。

• 存储方式的变化（行内vs行外存储）
  SQL Server对varchar(max)的存储逻辑是：如果数据长度≤8000字节且行有足够剩余空间，会直接存在行内的数据页；否则会存到单独的LOB页。原表有24个字段，行的基础长度已经接近8060字节的行大小限制，很多原本可以存在行内的小体积varchar(max)数据，被迫存到了LOB页。而新表只有两个字段（ID+varchar(max)），行的剩余空间充足，这些小数据会被存在行内。行内存储的LOB数据虽然没有LOB页的指针开销，但因为行长度变大，数据页的利用率会降低，间接导致空间占用增加。

• 压缩配置差异
  如果你给原表开启了行压缩或页压缩（SQL Server企业版/标准版支持），可变长度字段（包括varchar(max)）的存储空间会被大幅压缩，尤其是有重复内容或短数据的场景。如果新表没有开启同样的压缩配置，哪怕数据量更少，也可能因为缺少压缩而占用更多空间。

• 数据碎片问题
  原表的聚集索引可能是按某个连续字段（比如自增ID）排序，数据页的碎片率很低。而新表插入数据时是按原表的行ID顺序，如果原表的行ID存在不连续的情况（比如有过删除操作），新表的数据页会产生大量碎片，每个碎片页都会占用完整的页空间（默认8KB），导致整体空间浪费。

• NULL值的存储优化
  原表中varchar(max)为NULL的行，SQL Server用位图标记NULL状态，几乎不占用额外空间。而新表中所有行的varchar(max)都是非NULL的，每一行都需要存储数据的长度前缀和实际内容，这部分额外的开销积累起来也会增加空间占用。

• 重复数据删除配置
  如果你在原表开启了重复数据删除（仅企业版支持），相同的varchar(max)内容只会被存储一次，节省大量空间。如果新表没有开启这个功能，相同的数据会被多次存储，导致空间占用上升。

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