SQL server temporal table 学习笔记

refer:

https://blog.csdn.net/Hehuyi_In/article/details/89670462

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?view=sql-server-ver15https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?redirectedfrom=MSDN&view=sql-server-ver15

ef 目前不支持哦 (https://github.com/dotnet/efcore/issues/4693)

temporal table 是 2016 后有的.

目的是帮我做 history 管理.

数据嘛, 一直 update delete. 有时候发现问题想 check back history 从前我们需要之前维护, 设计表记入资料等等.

挺麻烦的，而已也没有规范.

好像是 2011 年后有个规范了. 然后 2016 sql server 就实现了这个规范. 就是这个 temporal table 了。

这个 version control 的机制实现方法就是为一个表做多一个 history 表

当原表发生修改时, 就计入到 history 表里面. 除了多一个表意外, 还有 2 个重要的 column 是 SysStartTime 和 SysEndTime

通过时间管理, 就可以还原任何时间点的数据快照了。

结构有了之后就是语句啦. sql server 有一些特定的语法可以轻松的 query 出历史数据.

做业务开发的我们不需要懂底层太多, history sql server 会自己维护, 我们 query 的时候也是通过特定语法直接从主表查询. 所以基本上对 history 表是没有什么感知的.

下面来试试看 :

首先 management studio 没有 ui 可以直接创建 temporal table. 但是它可以做一个 template query 来弄.

这里我简单写点测试就好

创建一个表

create table dbo.Abc
(
  Id int not null IDENTITY(1,1) PRIMARY KEY CLUSTERED ,
  name nvarchar(120) not null,
  age int not null,
  SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
  SysEndTime datetime2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
with (system_versioning = on (history_table = dbo.AbcHistory));

SysStartTime 和 SysEndTime 是必须的, 然后它是 datetime2 UTC 时间, 不可以设置成 datetimeoffset

如果想依据 timezone 来查询, 可以参考 https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-table-usage-scenarios?redirectedfrom=MSDN&view=sql-server-ver15

来试试跑些语句

insert into dbo.Abc (name, age) values ('keatkeat', 11);
update Abc set name = 'tamade' where Id = 1;
update Abc set name = 'tamade' where Id = 1;

insert 之后, start time 是当前时间, end time 是 9999 年, history 不会有任何资料

update 之后 start time 就更新了, 然后 history 就出现了一条 row, 这条 row 有之前的 value keatkeat 和那个 row 的 start time, end time 就是当前.

再 update 多一次的话 (哪怕没有改到) history 也是会多一条 row 的, 时间 formula 和上面一样.

delete 和 update 差不多, hisotry 多一条 row.

-- 主表的 end time 不知道有什么用, delete by used 怎样记入呢

查询的话很简单, 对主表 query 加上时间就可以了

指定时间

select * from Abc for system_time as of '2020-12-29 08:32:51.5585273'

多个指定时间

select * from Abc for system_time contained in ('2020-12-29 08:32:51.5585273', 2020-12-29 08:35:51.5585273)

between

select * from Abc for system_time between '2020-12-29 08:32:51.5585273' and '2020-12-30 08:32:51.5585273';

要 drop table 的话需要先 off

alter table dbo.Abc set (system_versioning = off);
drop table Abc;

大部分 alter table 的操作都是会自动同步到 history 的, 但有些不会啦. 以后真的用的时候才研究.