SQL Server数据仓库遗留事实表是否应创建聚集索引？

一、非唯一聚集索引使用Uniqueifier而非RID的原因 #

SQL Server的非唯一聚集索引之所以添加uniqueifier，而非直接采用堆表的RID（行标识符），核心逻辑是聚集索引的键是表的稳定逻辑行定位器：

• 堆表的RID是物理存储位置标识（文件号+页号+槽号），当堆表发生页分裂、数据移动时，RID会随之变化，若非聚集索引依赖RID，就需要频繁更新索引条目，性能开销极大。
• 聚集索引键（加uniqueifier后）是逻辑唯一标识，即便数据物理位置变动，这个逻辑标识不会改变，非聚集索引只需存储该逻辑键，避免了因物理位置变化导致的索引维护成本。
• 从设计原则上，SQL Server要求所有非聚集索引的行定位器必须具备持久性与稳定性，RID不满足这一要求，而聚集索引键+uniqueifier是符合标准的稳定逻辑标识。

二、不考虑空间时是否保留聚集索引 #

即便不担心空间占用，仍建议保留聚集索引，理由如下：

• 查询性能优势：聚集索引是有序结构，范围查询（如按日期过滤）、排序操作的性能远优于堆表，尤其你的表包含日期字段，这类高频查询场景能直接受益于聚集索引的有序性。
• 减少数据碎片：若新行按日期追加，聚集索引的有序插入特性不会产生页分裂；而堆表的插入是随机的，大表场景下极易产生大量碎片，后续维护成本极高。
• 非聚集索引稳定性：聚集索引的逻辑定位器比堆的RID更稳定，能降低非聚集索引的维护开销，哪怕是少量更新场景也能体现优势。

三、遗留表：聚集索引VS堆表的选择 #

结合你即将切换增量加载、新系统有代理主键、遗留数据无代理键的场景，更建议保留并调整聚集索引，而非改为堆表：

推荐方案：创建以日期字段为首列的非唯一聚集索引 #

• 新行按日期追加，完全匹配聚集索引的有序插入特性，不会产生页分裂，增量加载性能最优。
• 日期是高频查询过滤条件，聚集索引的有序性可极大加速这类查询，避免全表扫描或非聚集索引的键查找开销。
• 无需过度担心uniqueifier的影响：即便存在重复行，SQL Server自动添加的uniqueifier仅占用少量空间，对性能的影响远小于堆表RID的维护成本。

不推荐堆表的核心原因 #

• 堆表的非聚集索引依赖RID，数据移动时会导致非聚集索引的书签查找失效，需要更新大量索引条目，维护成本极高。
• 堆表在范围过滤、排序场景下的查询性能远不如有序的聚集索引，对于8000万行的大表，这个性能差距会非常显著。

四、关键字段变更时的唯一聚集索引处理 #

若必须创建唯一聚集索引且涉及关键字段变更（新旧字段共存），可按以下方式处理：

• 将新旧关键字段+日期字段共同作为唯一聚集索引的键列：既保证索引唯一性，又利用日期字段的有序性支持增量加载和范围查询。
• 待旧字段完全废弃后，在业务低峰期通过索引重建移除旧字段，避免影响正常业务查询。
• 若暂时无法确定唯一键的稳定性，优先选择非唯一聚集索引，后续再根据业务情况调整为唯一索引，避免因唯一键约束导致的数据加载失败。

五、代理主键的折中处理建议 #

虽然无法直接为遗留数据添加新系统的代理主键，可采取以下折中方案：

• 为遗留数据生成独立的代理主键范围（例如使用负数范围，新系统用正数），确保与新系统的代理键不重叠，后续加载新数据时沿用新系统的代理键。
• 若无法确定范围，可创建新的自增代理主键列，填充现有数据后，新系统数据加载时继续使用该自增列，放弃新系统的代理键（前提是业务允许）。
• 代理主键的核心价值是缩小非聚集索引的键大小，即便暂时无法添加，只要聚集索引列选择合理，查询性能也能得到有效保障。

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