我有三张系统版本化的时态表，我们把它们称作 FOO、BAR 和 BAZ。

它们的关联关系如下：

FOO -> 1-N -> BAR -> 1-N -> BAZ

我想为给定的 FOO 行检索完整的变更历史记录，包括它所关联的 BAR 和 BAZ 行的所有变更，作为一个统一的按时间顺序的时间线。

我当前的方法使用 FOR SYSTEM_TIME ALL，并基于时间段的重叠进行连接：

SELECT f.*, b.*, bz.*
FROM FOO FOR SYSTEM_TIME ALL f
JOIN BAR FOR SYSTEM_TIME ALL b
    ON b.FooId = f.FooId
    AND b.StartTime < f.EndTime
    AND b.EndTime   > f.StartTime
JOIN BAZ FOR SYSTEM_TIME ALL bz
    ON bz.BarId = b.BarId
    AND bz.StartTime < b.EndTime
    AND bz.EndTime   > b.StartTime
WHERE f.FooId = 1
ORDER BY f.StartTime, b.StartTime, bz.StartTime;

这是正确的做法吗？当三张表并非在完全相同的时间戳更新时，我担心会出现重复行和缺失的变更。

另外，我还注意到 FOO_History 包含在 StartTime 中的值，与子更新相匹配，即使没有任何 FOO 列发生变化——这是预期的吗？

解决方案

在不考虑性能因素的情况下，我认为你的查询基本上按原样成立，但需要做一个小调整，以确保 BAZ 行匹配 FOO 与 BAR 时间段的重叠部分。

SELECT f.*, b.*, bz.*
FROM FOO FOR SYSTEM_TIME ALL f
JOIN BAR FOR SYSTEM_TIME ALL b
    ON b.FooId = f.FooId
    AND b.StartTime < f.EndTime
    AND b.EndTime   > f.StartTime
JOIN BAZ FOR SYSTEM_TIME ALL bz
    ON bz.BarId = b.BarId
    AND bz.StartTime < LEAST(f.EndTime, b.EndTime)         -- Updated
    AND bz.EndTime   > GREATEST(f.StartTime, b.StartTime)  -- Updated
WHERE f.FooId = 1
ORDER BY f.StartTime, b.StartTime, bz.StartTime;

我确实考虑过是否可能更偏向于 ORDER BY GREATEST(f.StartTime, b.StartTime, bz.StartTime)，但想不出在哪些场景下会有差异。

至于性能，我在时态表索引方面并非专家，但我相信以下做法会提升查询性能：

CREATE INDEX IX_Bar_FooId ON Bar(FooId, EndTime, StartTime)
CREATE INDEX IX_Baz_BarId ON Baz(BarId, EndTime, StartTime)
CREATE INDEX IX_BarHistory_FooId ON BarHistory(FooId, EndTime, StartTime)
CREATE INDEX IX_BazHistory_BarId ON BazHistory(BarId, EndTime, StartTime)

你也应该了解 列存储索引。

虽然变更数据捕捉（Change Data Capture）有其用途，但要实现你想要的结果可能需要更多工作——要么维护扁平化的非规范化历史表，要么手动维护与时态表内置历史表同类的历史表。不过，如果这是用于审计/安全目的，最好有一个与主表分离的安全、只写一次、只读的历史记录。

关于时间戳的一致性，只要使用事务将相关的插入/更新/删除操作分组，这不会成为问题。来自时态表文档，开始时间和结束时间是基于“当前事务的开始时间”来设置的。

关于重复历史，请注意更新语句即使实际数据没有变化，也会生成新的历史记录。举例来说，语句 UPDATE Data SET Value = Value 将生成新的历史记录。

请参阅 这个db<>fiddle 以了解上述两点的示例。