SQLServer System-versioned Temporal Table - SQLServer时态表对比差异字段

SQLServer时态表能够在每次数据做更新，修改时备份当前记录到history表，这可以方便我们做数据追踪或数据恢复操作。时态表的具体创建查询可查看其他资料，这里介绍下如何使用时态表对比整理字段变化记录。

假设我们有一个std表，有id，name，age字段，使用下面script建立表及对应时态表。

CREATE TABLE [dbo].[Std] (
    [ID]           INT                                         IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (50)                               NULL,
    [Age]          INT                                         NULL,
    [RowStartTime] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
    [RowEndTime]   DATETIME2 (7) GENERATED ALWAYS AS ROW END   NOT NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    PERIOD FOR SYSTEM_TIME ([RowStartTime], [RowEndTime])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[Std_History], DATA_CONSISTENCY_CHECK=ON));

1.我们插入两条测试数据

INSERT INTO [dbo].Std VALUES (‘ben’,30);
INSERT INTO [dbo].Std VALUES (‘leo’,30);

2.此时Std有两条记录，Std_History时态表无记录

3.我们更新ben为ben_new,物理删除leo的数据

UPDATE [dbo].[Std] SET Name=‘ben_new’ where Name=‘ben’;
DELETE FROM [dbo].[Std] WHERE Name=‘leo’;

4.此时Std有一条ben的记录，leo记录不存在。Std_History时态表有两条记录，因为修改或删除时时态表备份了原纪录。

5.现在我们希望能追溯出Std表字段的修改记录，包含修改时间，修改字段，原值，新值。新建记录显示NULL–>值，删除记录显示值–>NULL

以ben的记录为例：
id changetime field oldval newval
2 2022/4/1 name NULL ben
2 2022/4/1 age NULL 30
2 2022/4/2 name ben ben_new

以leo的记录为例：
id changetime field oldval newval
2 2022/4/1 name NULL leo
2 2022/4/1 age NULL 30
2 2022/4/2 name leo NULL
2 2022/4/2 age 30 NULL

解决方案

建立视图SQL：

CREATE VIEW std_changes
AS
 
WITH h AS (
SELECT *, 0 AS rn
FROM  [dbo].[Std]
UNION ALL
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RowEndTime DESC, RowStartTime DESC)
FROM [dbo].[Std_History]
)

SELECT
ISNULL(old.ID, new.ID) AS ID
,ISNULL(old.RowEndTime, new.RowStartTime) AS ChangeTime
,CAST(ca.Field AS varchar(50)) AS Field
,ca.OldValue
,ca.NewValue
FROM h old
FULL JOIN h new ON old.ID = new.ID AND old.rn = new.rn + 1
CROSS APPLY (
VALUES
('Name', CAST(old.Name AS varchar(200)), CAST(new.Name AS varchar(200)))
,('Age', CAST(old.Age AS varchar(200)), CAST(new.Age AS varchar(200)))
) ca (Field, OldValue, NewValue)
WHERE (old.rn IS NULL OR old.rn <> 0)
AND ISNULL(OldValue,'') <> ISNULL(NewValue,'')
GO

测试结果：

注意：
时态表以UTC时间记录变化，界面展示时可根据情况转换为本地时间。
System-versioned temporal tables store values for period columns in UTC time zone, but you may find it more convenient to work in your local time zone, both for filtering data and displaying results. The following code sample shows how to apply a filtering condition, which is specified in the local time zone and then converted to UTC using AT TIME ZONE introduced in SQL Server 2016 (13.x):