带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
来源:互联网 发布:linux多线程机制 编辑:程序博客网 时间:2024/06/05 12:01
什么是 System-Versioned Temporal Table?
System-Versioned Temporal Table,暂且容我管它叫版本由系统控制的临时表,它是 SQL Server 2016 中的新型用户表,用于保留完整的数据更改历史记录。 它之所以称为版本由系统控制的临时表,是因为每一行的有效期由数据库引擎管理。
每个临时表有两个显式定义的列,其中每个列都有一个 datetime2 数据类型。每当数据修改后,系统将以独占方式使用这些列来记录每行的有效期。
除了这些列以外,该表还包含对使用镜像架构的另一个历史表的引用。 每当更新或删除了临时表中的某行后,系统将使用历史表来自动存储该行的先前版本。
这个临时表的推出,在一定程度上完全可以替代CDC,可用于ETL,追溯数据,审计等。之前CDC能用到的地方这个会更好用且更易维护。另外,以前约定让程序员更新数据时必须更新UpdateTime之类的约定也完全可以交由系统自己控制了。
创建语法:
CREATE TABLE MyTable( Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,MyCode CHAR(5),MyName NVARCHAR(200),RecordStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime)) WITH(SYSTEM_VERSIONING = ON);
建好后,在SSMS中是这个样子的:
由于没有指定历史表的名称,历史表自动被命名为MSSQL_TemporalHistoryFor_前缀再加上原始表的objectID.
删除表时必须先执行关闭表的系统版本开关,否则会报错:
ALTER TABLE MyTable SET (SYSTEM_VERSIONING = OFF)
关闭之后,在SSMS中都成了普通表。
删除之后以给定历史表的名字重建(这里历史表的名字指定时必须指定schema,否则会报错):
CREATE TABLE MyTable( Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,MyCode CHAR(5),MyName NVARCHAR(200),RecordStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime)) WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTableHistory));
插入数据:
INSERT INTO [MyTable]([MyCode],[MyName])VALUES('SH001','万剑齐发'),('SH002','ajiangg');SELECT * FROM [MyTable];
更新/删除数据后的结果:
UPDATE [MyTable] SET MyCode = 'SH003' WHERE ID = 1;DELETE FROM [MyTable] WHERE ID = 2;SELECT * FROM [MyTable];SELECT * FROM [MyTableHistory];
先关闭SYSTEM_VERSIONING,修改历史表名,并再次打开SYSTEM_VERSIONING,即完成了历史表的归档(当然,这样归档的话,归档的那部分数据也就失去了使用FOR SYSTEM_TIME语法查询的能力了):
ALTER TABLE MyTable SET (SYSTEM_VERSIONING = OFF);EXEC sp_rename 'MyTableHistory', 'MyTableHistory_20170303';ALTER TABLE MyTable SET (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.MyTableHistory));
追溯历史数据:
SELECT * FROM [MyTable]; SELECT * FROM [MyTableHistory]; SELECT * FROM [MyTable] FOR SYSTEM_TIME BETWEEN '2017-03-03 15:43:57.7006650' AND '9999-12-31 23:59:59.9999999'
追溯历史数据查询2(数据存在开始时间小于等于2017-03-03 15:43:57.7006650,且终止时间大于这个时间的Id为2的数据):
关于历史数据的追溯的新条件写法FOR SYSTEM_TIME ****,支持以下几种语法:
创建表时,关于GENERATED ALWAYS AS ROW START/END列还有个HIDDEN选项,这在一些我认为的不规范SQL写法中会有些区别(例如Insert时不指定插入的列,查询数据时使用select *等)
另外,不打开SYSTEM_VERSIONING开关的普通表,也能让GENERATED ALWAYS AS ROW START的标识列自动更新更新时间。(例如如下脚本中创建的表,UpdateTime列会被系统自动更新为最后一次更新的时间)
CREATE TABLE MyTable( Id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,MyCode CHAR(5),MyName NVARCHAR(200),UpdateTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,RecordEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,PERIOD FOR SYSTEM_TIME(RecordStartTime,RecordEndTime));
好了,关于System-Versioned Temporal Table,就介绍到这啦。
本文链接:http://www.cnblogs.com/ajiangg/p/6498873.html
参考链接:https://msdn.microsoft.com/en-us/library/dn935015.aspx
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 带版本号的符号 versioned symbol
- 由Beta版说开的-----版本控制
- 由const引发的版本控制问题
- 熟悉而又陌生的table
- Temporary Table 临时表的用法
- linux系统调用表(system call table)
- 临时表操作(# table)
- 开源,从熟悉版本控制软件开始
- 经典!Linux由内核、shell、文件系统和应用程序四部分组成,带你走进嵌入式Linux系统的内部
- 由git版本控制maven管理的简单web…
- 熟悉jquery的版本基础
- android从源码带你熟悉DigitalClock 数字时针的应用以及它的使用场景
- 带你领略Linux系统发展及版本更迭
- 李开复:未来看好这五大领域!
- 原生js轮播效果demo
- js中根据后台返回结果动态创建表
- PAT (Advanced Level) 1124. Raffle for Weibo Followers (20) 解题报告
- 训练19-HTML 给标签增加圆角边框
- 带你熟悉SQLServer2016中的System-Versioned Temporal Table 版本由系统控制的临时表
- 杭电2056之Rectangles 杭电
- Another app is currently holding the yum lock; waiting for it to exit解决方法
- python虚拟环境virtualenv
- PAT (Advanced Level) 1125. Chain the Ropes (25) 解题报告
- 试水用markdown写博客
- js中innerText/value/innerHTML三个属性的区别
- 训练20-HTML 给图像设置圆角边框
- android 服务service