前言

之前写过一篇, 但那个时候还没有开始用, 现在是要用了, 所以翻新一下呗.

SQL server temporal table 学习笔记

 

主要参考:

官网 Temporal tables

[译]SQL SERVER 2016 – Temporal Tables

SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表）

 

用处

Find back history

Temporal table 会做多一个 history table, 把所有修改/删除的资料存起来, 所以任何数据都不会丢失. 要找回也比较容易. 

Time Travel

任何修改/删除的资料都会有时间记入, 通过 build-in 的 query 语句, 我们可以快速查询某个时间点的数据. (俗称 – 时间旅行)

参考:

Temporal table usage scenarios

 

代价

1.会拉慢 update, delete 执行, 因为执行多了一个把资料 insert to history table 的动作.

2.会增加 disk space

 

与之相关

Temporal table 经常和 Change Data Capture (CDC), Change Tracking (CT), Event Sourcing, Soft Delete, Audit Trail 一起讨论.

我对 CDC, CT, Event Sourcing 不太清楚, 就不说了.

Soft Delete 应该算是一种比较简单实现的 find back history 方案. 与 Temporal Table 比的话, 区别是

Temporal Table 是 SQL Server build-in 的方案, 有特定的 query 语句, 整个 update, delete 过程都是封装的. 

Soft Delete 则只是在简单的 SQL 语句上做了一些规则去实现一部分 Temporal Table 的功能而已. Soft Delete 的优势只有一个就是它简单.

Temporal Table 可以帮助解决一些 Audit Trail 的问题, 如果我们的 tracking 范围只是针对数据改动的话, 它是 ok 用的, 但一般上我们会需要 tracking 整个 application 的使用, 而不仅仅是数据库的改动.

参考:

SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 1

SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 2

SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 3

Event sourcing vs SQL Server Temporal Tables

 

历史

Temporal Table 是 2011 年才有的规范, SQL Server 实现是在 2016 版本.

 

How It Work

当我们创建一个 Temporal Table 时, SQL Server 会同时创建一个对应的 history table. 每一条 row 都需要记入 2 个 columns, ValidFrom 和 ValidTo (Time Travel 就靠这个完成的)

当 insert 数据时, ValidFrom 就是当下, ValidTo 是 9999 年. (insert 数据对 history table 是不会有任何作用的, 只有 update 和 delete 才会)

当 update 数据时, 先把要 update 的 row clone to the history table, 然后设置 ValidTo 为当下, 接着 update row 同时把 ValidFrom 设置成当下. (注: 不管值是否有真的变化, 只要 update 语句执行, history table 就会多一条 row)

当 delete 数据时, 先把要 delete 的 row clone to the history table, 然后设置 ValidTo 为当下, 接着 delete row.

query 的时候通过 join history table + 过滤 ValidFrom 和 ValidTo 就可以拿到任何时间点上的数据了.

上面这个过程都是封装起来的, 我们基本上就是像用普通表一样执行 Create, Update, Delete 就可以了. 而在 Select 的时候会有一些特制的 query 语句来查询 history table.

 

Naming Conversion

ValidFrom (SQL Server doc) = SysStartTime (SQL Server docs) = PeriodStart (EF core docs)

ValidTo (SQL Server doc) = SysEndTime (SQL Server docs) = PeriodEnd (EF core docs)

 

实战

创建 Temporal Table

CREATE TABLE dbo.Person
(
  Id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  Name nvarchar(120) NOT NULL,
  Age int NOT NULL,
  ValidFrom datetime2 GENERATED ALWAYS AS ROW START,
  ValidTo datetime2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory));

关键就是多了 ValidFrom, ValidTo 然后 SYSTEM_VERSIONING = 0, 附上 history table 名字就可以了

出来后长这样, Person table 的 icon 换了, 里头也多了一个 PersonHistory table

SQL Server – Temporal Table 时态表_数据

 

Insert data

INSERT INTO Person (Name, Age) VALUES ('xin yao', 10);
SELECT * FROM Person;
SELECT * FROM PersonHistory;

Insert data 不会对 history table 有任何副作用. 

SQL Server – Temporal Table 时态表_数据_02

 

Update Data

UPDATE Person SET AGE = 15 WHERE Id = 1;
SELECT * FROM Person;
SELECT * FROM PersonHistory;

History table 有历史记入了. 它们的时间也更新了.

SQL Server – Temporal Table 时态表_封装_03

 

Delete Data

DELETE FROM Person WHERE Id = 1;
SELECT * FROM Person;
SELECT * FROM PersonHistory;

History table 又多了一条, 时间也更新了

SQL Server – Temporal Table 时态表_数据_04

 

Select data

全部资料调出来

SELECT * FROM Person FOR SYSTEM_TIME ALL;

调出某个时间点的资料

SELECT * FROM Person FOR SYSTEM_TIME AS OF '2021-10-19 06:00:39.2906582';

这个就有点像我们 git checkout 到某个 commit 那样.

它的 WHERE 语句时:

ValidFrom <= date_time AND ValidTo > date_time

调出某个期间的资料

SELECT * FROM Person FOR SYSTEM_TIME FROM '2021-10-19 05:59:11.5214221' TO '2021-10-19 06:00:39.2906583';
SELECT * FROM Person FOR SYSTEM_TIME BETWEEN '2021-10-19 05:59:11.5214221' AND '2021-10-19 06:00:39.2906583';

这 2 个其实很像, 所以一起讲吧.

它的 WHERE 语句是: 

ValidFrom < end_date_time AND ValidTo > start_date_time (FROM, TO)

ValidFrom <= end_date_time AND ValidTo > start_date_time (BETWEEN AND)

调出某个期间内的资料

SELECT * FROM Person FOR SYSTEM_TIME CONTAINED IN ('2021-10-19 05:59:11.5214221','2021-10-19 06:00:39.2906583');

它的 WHERE 语句是: 

ValidFrom >= start_date_time AND ValidTo <= end_date_time

 

Drop Temporal Table

先 OFF, 然后分别删除 2 个 table

ALTER TABLE Person SET (SYSTEM_VERSIONING = OFF);
DROP TABLE Person;
DROP TABLE PersonHistory;

 

创建 for existing table

参考: Alter non-temporal table to be a system-versioned temporal table

目前我没有这个需求, 所以暂时不研究了, 因为它有一些 important remark 要 take care, 但我看不太懂.