SQLServer2016新特性（2）：时态表TemporalTable
SQL Server 2016 引⼊了对版本由系统控制的临时表的⽀持，其附带的内置⽀持可以提供表中存储的数据在任意时间点的相关信息，⽽不仅仅是数据在当前时刻正确的信息。 临时表是 ANSI SQL 2011 中引⼊的数据库功能。
版本由系统控制的临时表是⽤户表的⼀种类型，旨在保留完整的数据更改历史记录，并实现轻松的时间点分析。 这种类型的临时表之所以称为版本由系统控制的临时表，是因为每⼀⾏的有效期由系统（即数据库引擎）管理。
⼀、创建时态表
在创建时态表的时候，需要增加3个字段：
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
server 2016SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime)
每个临时表有两个显式定义的列，其中每个列都有⼀个 datetime2 数据类型。 这些列称为期限列。 每当修改了某⾏后，系统将以独占⽅式使⽤这些期限列来记录每⾏的有效期。
也就是说这2个字段是系统使⽤的，我们不需要去修改，⽤来记录每⼀⾏数据的开始时间、⽆效时间，period就是这个从有效到⽆效的期间、时间段。
代码如下：
CREATE TABLE tb_org
(
org_id INT NOT NULL PRIMARY KEY CLUSTERED,
org_name VARCHAR(30) NOT NULL,
parent_org_id INT NULL,
emp_id INT 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)
INSERT INTO tb_org(org_id,org_name,parent_org_id,emp_id)
VALUES
(1, '⾏长', NULL,1),
(2, '常务', 1,2),
(3, '专务', 1,3),
(4, '营业⼆部部长', 3,4),
(5, '营业⼆部次长', 4,5);
⼆、修改数据，查询修改之前的数据
修改数据，把emp_id改为6：
UPDATE tb_org
SET emp_id = 6
WHERE org_id = 5
查询修改后的数据，如下图：
接下来，查询修改之前的数据，需要注意的是datetime2记录的是utc时间，⽽不是本地的时间，所以需要把下⾯本地的时间2018-01-27 16:46:43 减去8⼩时的时差，转成utc时间就是 2018-01-27 08:46:43。
代码如下：
SELECT *
FROM tb_org
FOR SYSTEM_TIME BETWEEN '2018-01-27 00:00:00.0000000' AND '2018-01-27 08:46:43'
WHERE org_id = 5;
查询结果中是修改之前的数据，emp_id为5：
三、这个修改前的数据是存在哪⾥的？
在ssms中，点开tb_org，可以看到有⼀个历史表：[dbo].[MSSQL_TemporalHistoryFor_1317579732]
查⼀下这个表，发现正是这个表⾥记录了修改之前的数据：
最后，如果要删除tb_org，需要进⾏如下操作后，才能删除表，否则会报错：
DROP TABLE tb_org
/*消息 13552，级别 16，状态 1，第 30 ⾏
在表“test.dbo.tb_org”上删除表操作失败，因为此操作不是经系统版本控制的临时表中⽀持的操作。*/
--必须要先设置
ALTER TABLE dbo.tb_org SET (SYSTEM_VERSIONING=OFF);
DROP TABLE tb_org;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。