如何基于SQL变更日志表重建实体记录的最新完整状态

优化实现方案 #

你原有的多CTE关联方案最大的问题是需要对Changes表做多次重复扫描，10个字段就要触发10次表扫描，数据量较大时性能会非常差。更优的方案是单次扫描+条件聚合，仅需1次表扫描即可得到所有字段的最新值，实现代码如下：

CREATE VIEW RecordView AS
WITH LatestFieldValues AS (
    SELECT 
        RecordId,
        Field,
        Val,
        -- 按记录ID+字段ID分组，按变更时间倒序取最新的变更值
        ROW_NUMBER() OVER (PARTITION BY RecordId, Field ORDER BY DateOfChange DESC) AS rn
    FROM Changes
)
SELECT 
    r.Id AS RecordId,
    r.AUserGeneratedIdentifer,
    -- 每个字段对应一个条件取值，按需做类型转换
    MAX(CASE WHEN l.Field = 1 AND l.rn = 1 THEN TRY_CAST(l.Val AS DATETIME) END) AS DateOpened,
    MAX(CASE WHEN l.Field = 2 AND l.rn = 1 THEN TRY_CAST(l.Val AS DATETIME) END) AS DateClosed,
    -- 其余字段按照对应Field的枚举值补充即可，非日期类型可以去掉类型转换
    MAX(CASE WHEN l.Field = 3 AND l.rn = 1 THEN l.Val END) AS OtherField
FROM Records r
LEFT JOIN LatestFieldValues l ON r.Id = l.RecordId
GROUP BY r.Id, r.AUserGeneratedIdentifer;

注：上面CASE语句中的1、2、3为你定义的字段枚举值，替换为实际的Field取值即可。

这个方案的优势非常明显：

• 仅扫描Changes表1次，相比10个CTE的方案减少9次表扫描开销
• 用LEFT JOIN而非INNER JOIN，兼容没有任何变更记录的实体，不会漏数据
• 用TRY_CAST做类型转换，避免变更表中非法值导致整个查询报错
• 扩展性强，如果需要查询历史某一时间点的实体快照，仅需在LatestFieldValues的CTE中增加WHERE DateOfChange <= '目标时间'的过滤条件即可。

原多CTE方案的性能影响 #

1. 重复扫描开销：每多一个字段就多一次全表/索引扫描，10个字段就要扫描10次Changes表，数据量越大性能损耗越夸张，100万行的表相当于要处理1000万行数据
2. 关联开销：多个CTE和主表关联时需要做多次JOIN操作，CPU和内存开销都会显著上升
3. 数据丢失风险：如果用INNER JOIN，只要某一个字段没有变更记录，整条实体数据都会被过滤，不符合预期

额外性能优化建议 #

如果数据量较大，可以给Changes表加覆盖索引，不需要回表即可完成所有计算，性能提升非常明显：

CREATE NONCLUSTERED INDEX IX_Changes_RecordId_Field_DateOfChange 
ON Changes (RecordId, Field, DateOfChange DESC)
INCLUDE (Val);

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