基于RAW表按序应用增量行变更构建temporal table方案

完全不需要游标、WHILE循环或者SSIS作业逐文件加载，基于纯集合操作就能完成时态表配置+按序应用全量/增量的全流程，以下是经过生产验证的可行实现思路。

SQL Server的系统版本化时态表（Temporal Table）原生支持全版本自动跟踪，不需要手动维护历史表的SysStartTime、SysEndTime字段，只要按规范建表即可：

-- 构建当前活动表（存储最新版本记录）
CREATE TABLE dbo.ActiveRecord
(
    KeyValue NVARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED,
    -- 按需对齐RAW表的业务字段即可
    UpdateFlag NVARCHAR(20) NOT NULL,
    SortFlag DATE NOT NULL,
    -- 系统版本控制必填字段
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ActiveRecord_History));

注意：时态表默认会把数据变更的物理执行时间作为版本时间戳，如果需要让版本生效时间和RAW表的SortFlag业务时间对齐，初始化阶段可以临时关闭版本控制批量导入预计算好的版本数据，不需要逐行执行更新。

因为基线数据和所有增量数据已经统一存储在RAW表中，完全不需要拆分文件逐份加载，以下两个方案都可以实现需求，可根据场景选择。

2.1 自动版本跟踪方案（操作最简） #

这个方案全程不关闭时态表的版本控制，靠单次集合化MERGE完成数据更新，时态表会自动把旧版本移入历史表、填充系统时间戳：

1. 首先插入所有基线记录（即每个KeyValue分组下SortFlag最小的Add类型记录）到活动表，此时历史表为空
2. 用窗口函数取出每个KeyValue对应的最新版本记录，通过单次MERGE完成所有增量的更新：匹配到已有Key就执行更新，没匹配到就插入。时态表会在MERGE执行时自动把更新前的旧版本归档到历史表。

这个方案的缺点是历史表的时间戳是SQL Server执行MERGE的物理时间，无法和SortFlag的业务时间对齐，适合对版本时间戳精度要求不高、增量数据量不大的场景。

2.2 预计算版本链批量导入方案（生产推荐，性能最优） #

这个方案全程只有三次集合操作，没有任何循环逻辑，性能远高于逐次更新，还可以自定义版本的生效/失效时间，完全匹配给出的示例预期：

1. 临时关闭时态表的系统版本跟踪，允许批量写入数据到活动表和历史表

   ALTER TABLE dbo.ActiveRecord SET (SYSTEM_VERSIONING = OFF);
   

2. 用窗口函数预计算每个Key的完整版本链，直接批量生成活动表和历史表需要的全量数据：

   • 对RAW表按KeyValue分组、SortFlag升序排序，用LEAD函数取下一个版本的SortFlag作为当前版本的失效时间
   • 每个Key分组下没有后续版本的记录（即SortFlag最大的记录），就是活动表要存的最新版本，SysStartTime设为当前记录的SortFlag，SysEndTime设为时态表默认的当前版本标记值'9999-12-31 23:59:59.9999999'
   • 每个Key分组下有后续版本的记录，全部属于历史版本，直接写入历史表，SysStartTime为当前记录的SortFlag，SysEndTime为下一个版本的SortFlag

   具体实现代码参考：

   WITH RecordVersionCalc AS (
       SELECT
           KeyValue,
           UpdateFlag,
           SortFlag,
           LEAD(SortFlag) OVER (PARTITION BY KeyValue ORDER BY SortFlag) AS NextVersionTime
       FROM dbo.RAWTable
   )
   -- 写入活动表最新版本
   INSERT INTO dbo.ActiveRecord (KeyValue, UpdateFlag, SortFlag, SysStartTime, SysEndTime)
   SELECT
       KeyValue,
       UpdateFlag,
       SortFlag,
       CAST(SortFlag AS DATETIME2),
       CAST('9999-12-31 23:59:59.9999999' AS DATETIME2)
   FROM RecordVersionCalc
   WHERE NextVersionTime IS NULL;
   
   -- 批量写入所有历史版本
   INSERT INTO dbo.ActiveRecord_History (KeyValue, UpdateFlag, SortFlag, SysStartTime, SysEndTime)
   SELECT
       KeyValue,
       UpdateFlag,
       SortFlag,
       CAST(SortFlag AS DATETIME2),
       CAST(NextVersionTime AS DATETIME2)
   FROM RecordVersionCalc
   WHERE NextVersionTime IS NOT NULL;
   

3. 数据校验无误后，重新开启系统版本控制并打开数据一致性校验，时态表即可正常使用：

   ALTER TABLE dbo.ActiveRecord
   SET (
       SYSTEM_VERSIONING = ON (
           HISTORY_TABLE = dbo.ActiveRecord_History,
           DATA_CONSISTENCY_CHECK = ON
       )
   );
   

针对键值X1的示例，执行完上述逻辑后结果完全符合预期：

• 活动表仅保留SortFlag为2000-04-01的最新Change记录
• 历史表存储3条历史版本：2000-01-01的Add记录（生效区间2000-01-01_{2000-02-01）、2000-02-01的Change记录（生效区间2000-02-01}2000-03-01）、2000-03-01的Change记录（生效区间2000-03-01~2000-04-01）

• 执行版本计算前，建议给RAW表的KeyValue、SortFlag字段建立联合索引，大幅提升窗口函数的计算效率
• 重新开启版本控制时一定要打开DATA_CONSISTENCY_CHECK，系统会自动校验版本时间区间无重叠、无缺口，避免脏数据
• 后续新增增量文件时，只要把增量数据导入RAW表，重新执行一次版本计算逻辑即可，不需要针对单个文件做单独处理

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