SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15193字，纯文字阅读大概需要22分钟。
内容图文
1）审计数据改动，为报表和数据分析提供支持，洞察记录的变化趋势
2）实现了ETL中的Slowly Changing Dimension的类型2（保留所有数据的旧版本）
3）一旦发生误操作的情况下可以及时进行数据恢复
Temporal Table（历史表）和CDC的区别
以前微软为ETL提供了CDC功能来记录数据改动。Temporal Table同样是用于记录数据改动，但是它俩不一样。第一点，Temporal Table不像CDC是基于事务日志，它是作为事务的一部分被提交的。第二点，CDC是每次对新的表记录最新版本拷贝一份到另外一张表，而Temporal Table是把旧版本的记录转移到另外一张表，只有把Temporal Table和当前表的记录合并才可以构成表的整个历史版本（记录没有被删除的情况下）。
Temporal Table（历史表）的条件？
1）必须有主键；2）两个记录有效时间范围字段必须为not null；3）历史表必须是和主表在结构上一模一样，包括字段名字和数据类型；
Temporal Table（历史表）如何实现？
Temporal Table其实对一对数据库表进行数据版本化（System-versioning）。一张是主表，一张是主表的历史记录表。Temporal Table的条件之一是添加两个类型为datetime2的字段来标示记录的有效时间范围 -- SysStartTime和SysEndTime。这两个字段是有系统自动更新的，可以选择在建表的时候对字段加入HIDDEN提示把字段隐藏，这样就避免在SELECT * FROM或者INSERT INTO的时候出现两个字段在列表里面。当插入（insert）发生时，事务开始的时间作为主表的SysStartTime，SysEndTime则被更新为9999-12-31，历史表不会有任何变化。当更新（update）发生时，历史记录表中的SysEndTime被更新为事务开始的时间，主表的SysStartTime则被更新为事务开始的时间，SysEndTime则被更新为9999-12-31。当删除（delete）发生时，历史记录表中的SysEndTime被更新为事务开始的时间。
查询Temporal Table（历史表）的记录
SQL Server对T-SQL提供了几个新的子句用于查询Temporal Table中的记录，即在正常的T-SQL查询语句后面添加新的子句：
FOR SYSTEM_TIME ALL, AS OF, BETWEEN...AND, FROM...TO, CONTAINED IN
这里如果SysStartTime和SysEndTime相等时不会返回记录的。
AS OF <date_time> 等于SysStartTime<= date_time AND SysEndTime> date_time
FROM <start_date_time> TO <end_date_time> 等于SysStartTime< end_date_time ANDSysEndTime> start_date_time
BETWEEN <start_date_time> AND <end_date_time> 等于SysStartTime<= end_date_time ANDSysEndTime> start_date_time
CONTAINED IN(<start_date_time> ,<end_date_time>) 等于SysStartTime>= start_date_time ANDSysEndTime<= end_date_time
ALL 等于没有任何筛选条件
下图是来自MSDN的一张图，我觉得用于描述Temporal Table（历史表）的工作流程非常确切
Temporal Table（历史表）的最佳实践？
1）如果是做数据分析，比如统计一个平均值或者总数这样的分析，在History表的主键上建聚集列存储索引（clustered columnstore index）；
2）如果是做审计，对数据行有效时间范围字段建聚集索引，然后对主键也建立索引；
创建Temporal Table（历史表）
History table是不会出现在SQL Server Management Studio的Object Explorer窗口的，但是你可以通过sys.tables找出来。
Temporal Table（历史表）可以有三种方法创建：1）你完全不关心名字，让SQL Server帮你创建包括帮你自动生成表名；2）你指定表名然后让SQL Server根据表名为你生成表结构；3）你事先创建好表；
由SQL Server自动创建History表
CREATE TABLE dbo.TemporalTableTEST1 ( ID INT PRIMARY KEY CLUSTERED , 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);
自己指定表名字和自己事先创建好表都是一样的语法。如果表已经存在，表的结构会被检查。检出出问题命令失败。
CREATE TABLE dbo.TemporalTableTEST2 ( ID INT PRIMARY KEY CLUSTERED , 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 (HISTORY_TABLE = dbo.TemporalTableTEST2_History));
因为事先创建好的表可能已经存在数据行，建议添加好DATA_CONSISTENCY_CHECK来同时检查表中的数据行。建议在事先创建好表的情况下添加DATA_CONSISTENCY_CHECK选项
CREATE TABLE dbo.TemporalTableTEST3 ( ID INT PRIMARY KEY CLUSTERED , 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 (HISTORY_TABLE = dbo.TemporalTableTEST3_History, DATA_CONSISTENCY_CHECK = ON));
如果是对一张现有表进行转换，要分两种情况：一种是表是空表，一种是表里面已经存在数据行。下面是对一张空表转换成Temporal Table的例子
--DROP TABLE dbo.TemporalTableTEST5 CREATE TABLE dbo.TemporalTableTEST5 ( ID INT PRIMARY KEY CLUSTERED ) GO SELECT * FROM dbo.TemporalTableTEST5 GO ALTER TABLE dbo.TemporalTableTEST5 ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START CONSTRAINT DF_TemporalTableTEST5_SysStart DEFAULT SYSUTCDATETIME() , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END CONSTRAINT DF_TemporalTableTEST5_SysEnd DEFAULT CONVERT(datetime2 (0), ‘9999-12-31 23:59:59.9999999‘), PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) GO ALTER TABLE dbo.TemporalTableTEST5 SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableTEST5_History, DATA_CONSISTENCY_CHECK = ON)); GO
如果表中有数据，需要分开来完成这个转换过程。
--DROP TABLE dbo.TemporalTableTEST4 CREATE TABLE dbo.TemporalTableTEST4 ( ID INT PRIMARY KEY CLUSTERED ) GO INSERT INTO dbo.TemporalTableTEST4(ID) VALUES(1),(2),(3) GO --INSERT INTO dbo.TemporalTableTEST4(ID) --VALUES(4),(5),(6) --GO ALTER TABLE dbo.TemporalTableTEST4 ADD SysStartTime DATETIME2 NOT NULL CONSTRAINT DF_TemporalTableTEST4_SysStart DEFAULT SYSUTCDATETIME() , SysEndTime DATETIME2 NOT NULL CONSTRAINT DF_TemporalTableTEST4_SysEnd DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.9999999‘) GO SELECT * FROM dbo.TemporalTableTEST4 GO --UPDATE dbo.TemporalTableTEST4 SET SysEndTime = ‘9999-12-31 23:59:59.9999999‘ --GO --ALTER TABLE dbo.TemporalTableTEST4 --ALTER COLUMN SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START --ALTER TABLE dbo.TemporalTableTEST4 --ALTER COLUMN SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END ALTER TABLE dbo.TemporalTableTEST4 ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime); GO ALTER TABLE dbo.TemporalTableTEST4 SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableTEST4_History, DATA_CONSISTENCY_CHECK = ON)); GO
如果你直接用第一种办法就会收到一个错误提示
Msg 13575, Level 16, State 0, Line 53 ADD PERIOD FOR SYSTEM_TIME failed because table ‘JerryDB.dbo.TemporalTableTEST4‘ contains records where end of period is not equal to MAX datetime.
为了证明SQL Server只要求主表和历史表的字段结构和约束一致，不要求分区和压缩选项一致，这里做一个实验
CREATE PARTITION FUNCTION myPF (int) AS RANGE LEFT FOR VALUES (1, 100, 1000); GO CREATE PARTITION SCHEME myPS1 AS PARTITION myPF TO ( [primary], [primary], [primary], [primary] ); --DROP TABLE dbo.TemporalTableTEST7_History CREATE TABLE dbo.TemporalTableTEST7_History ( ID INT NOT NULL , SysStartTime DATETIME2 NOT NULL , SysEndTime DATETIME2 NOT NULL ) ON myPS1(ID) WITH (DATA_COMPRESSION = PAGE ) CREATE TABLE dbo.TemporalTableTEST7 ( ID INT PRIMARY KEY CLUSTERED , 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 (HISTORY_TABLE = dbo.TemporalTableTEST7_History));
上面是可行的。
总结一下：
总之记住几个点
1）PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)和SYSTEM_VERSIONING都是Temporal Table的特性，但是它俩在某些方面是不互相依赖。SYSTEM_VERSIONING是起到启动历史表的作用，PERIOD FOR SYSTEM_TIME是为标示表记录有效时间范围而存在，属于表结构属性的范畴。
2）PERIOD FOR SYSTEM_TIME隐式的将连个SYSTEM TIME的字段转换成AS ROW STARTS和AS ROW ENDS
3）SYSTEM_VERSIONING是不会阻止你去更新主键的，所以一旦你更新了主键，将会导致主表和历史表的记录错乱；
4）虽然PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)和SYSTEM_VERSIONING在某些方面不互相依赖，但是要更新SYSTEMTIME字段，需要SET SYSTEM_VERSIONING = OFF
对PARTITION SWITCH的支持
条件是stage表需要有SYSTEMTIME PERIOD，但是不要求表必须是SYSTEM_VERSIONING。这里对主表和历史表的限制是
主表：在SYSTEM_VERSIONING=ON的情况下SWITCH OUT是不允许的，我们都知道PARTITION SWITCH仅是元数据（metadata）的变动，这样History表是捕捉不到分区内数据的，所以行不通；SWITCH IN在SYSTEM_VERSIONING=OFF的情况下是可以进行的，毕竟这样也不会对History表有什么影响，因为SWITCH IN相当于INSERT行为，INSERT对于History表没有影响。
历史表：SWITCH OUT可以在SYSTEM_VERSIONING=ON的情况下进行；SWITCH IN在SYSTEM_VERSIONING=ON的情况下则不行，因为这本身就违反了History表的数据验证流程；
注：
这里所说的Data Consistency检查只是检查是否SysStartTime<=SysEndTime
对Temporal Table的主表的结构改动
这一步SQL Server倒是做得挺好的，就是不需要你改完主表还要去改历史表。比如你添加一个字段和一个默认约束到主表，历史表也自动应用到同样的改动。
ALTER TABLE dbo.TemporalTableTEST4 ADD Name NVARCHAR(100) NOT NULL CONSTRAINT DK_TemporalTableTEST4_Name DEFAULT ‘Jerry‘ GO
select * from dbo.TemporalTableTEST4
select * from dbo.TemporalTableTEST4_History
结果
某些情况下我们可以不停用SYSTEM_VERSIONING的情况下照样完成了对主表的结构改动，比如添加一个正常的字段（非compted等），但是如果添加诸如identity或者computed字段则需要停用system_versioning。否则
Msg 13724, Level 16, State 1, Line 135 System-versioned table schema modification failed because adding computed column while system-versioning is ON is not supported.
但是有一点是例外，就是如果你要删除主表的一个字段，除了要删除主表字段上创建的约束外还要删除历史表上对应字段的约束，否则
Msg 5074, Level 16, State 1, Line 142 The object ‘DF__TemporalTa__dttm__02FC7413‘ is dependent on column ‘dttm‘. Msg 4922, Level 16, State 9, Line 142 ALTER TABLE DROP COLUMN dttm failed because one or more objects access this column.
查询数据
上面讲了FOR SYSTEM_TIME的五个子句 AS OF | FROM...TO | BETWEEN...AND | CONTAINED IN (<START>,<END>) | ALL
理解这几个其实很容易，完全无需去记住他们所应用的WHERE表达式。
ALL = 全部嘛，这个不用讲
AS OF = AS OF的英文意思是自...开始，那就是某个时间点有效（包括这个时间点）的行
FROM... TO = 时间区间内有效的行，但是不包含开闭的时间点，即不包含上限
BETWEEN...AND = 时间区间内有效的行，包含开的时间点，即包含下限
CONTAINED IN (<START>,<END>) = CONTAINED的意思是包含，也就是说记录有效区间处在我们指定的时间区间这个容器内
拿上面的TemporalTableTEST5来demo。先准备好数据。
ALTER TABLE dbo.TemporalTableTEST5 ADD float_col FLOAT GO INSERT INTO dbo.TemporalTableTEST5(ID, float_col) VALUES(1,100),(2,200),(3,300) GO UPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50 WHERE ID = 1 UPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50 WHERE ID = 1 UPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50 WHERE ID = 1
查询全部的历史数据
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME ALL
ID float_col SysStartTime SysEndTime 1 250 2016-02-21 09:27:52.0379197 9999-12-31 23:59:59.9999999 2 200 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 3 300 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 1 100 2016-02-21 09:27:38.6363057 2016-02-21 09:27:40.5162446 1 150 2016-02-21 09:27:40.5162446 2016-02-21 09:27:46.4312559 1 200 2016-02-21 09:27:46.4312559 2016-02-21 09:27:52.0379197
AS OF
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME AS OF ‘2016-02-21 09:27:38.6363057‘;
ID float_col SysStartTime SysEndTime 2 200 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 3 300 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 1 100 2016-02-21 09:27:38.6363057 2016-02-21 09:27:40.5162446
FROM...TO
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME FROM ‘2016-02-21‘ TO ‘2016-02-21 09:27:38.6363057‘ SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME FROM ‘2016-02-21 09:27:40.5162446‘ TO ‘2016-02-22‘
第一个没有记录返回
第二个返回了
ID float_col SysStartTime SysEndTime 1 250 2016-02-21 09:27:52.0379197 9999-12-31 23:59:59.9999999 2 200 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 3 300 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 1 150 2016-02-21 09:27:40.5162446 2016-02-21 09:27:46.4312559 1 200 2016-02-21 09:27:46.4312559 2016-02-21 09:27:52.0379197
把FROM...TO的例子替换成BETWEEN...AND
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME BETWEEN ‘2016-02-21‘ AND ‘2016-02-21 09:27:38.6363057‘ SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME BETWEEN ‘2016-02-21 09:27:40.5162446‘ AND ‘2016-02-22‘
第一个返回了
ID float_col SysStartTime SysEndTime 2 200 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 3 300 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 1 100 2016-02-21 09:27:38.6363057 2016-02-21 09:27:40.5162446
第二个返回了
ID float_col SysStartTime SysEndTime 1 250 2016-02-21 09:27:52.0379197 9999-12-31 23:59:59.9999999 2 200 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 3 300 2016-02-21 09:27:38.6363057 9999-12-31 23:59:59.9999999 1 150 2016-02-21 09:27:40.5162446 2016-02-21 09:27:46.4312559 1 200 2016-02-21 09:27:46.4312559 2016-02-21 09:27:52.0379197
还是上面的例子改写，变成CONTAINED IN
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME CONTAINED IN (‘2016-02-21‘, ‘2016-02-21 09:27:38.6363057‘) SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5 FOR SYSTEM_TIME CONTAINED IN (‘2016-02-21 09:27:40.5162446‘, ‘2016-02-22‘)
结果就是第一个没有返回任何结果
第二个返回了
ID float_col SysStartTime SysEndTime 1 150 2016-02-21 09:27:40.5162446 2016-02-21 09:27:46.4312559 1 200 2016-02-21 09:27:46.4312559 2016-02-21 09:27:52.0379197
这个语法同样适用于UPDATE
讲了这么多，Temparal Table还是有需要方面可讲的，比如它对In-memory OLTP Optimized Table的支持啦，比如安全的考虑啦。真要将估计很多。姑且到这。今后有机会再深究下。最后，在这里思考下到底这个东西在现实生产环境中可以怎么好好利用或者结合其他的特性一起发挥它的最大价值呢？
1）首先我觉得基于上面讲到的可以作为数据误操作的数据复原。Temparal Table 结合SQL Server Audit。Temparal Table实现记录历史记录改动，而SQL Server Audit提供了对用户行为的审计。两者通过时间来关联。这样我们就是当初这条旧的历史版本记录是被谁改动或者删除的。然后对SQL Server Audit加载的目标表创建聚集索引到时间行以及以时间字段创建分区表。再创建非聚集索引到Object字段，再结合PAGE COMPRESSION压缩SQL Server Audit加载的目标表的数据行。
2）报表分析这个案例我觉得视情况而定，要看到底是为了查看某条或者若干记录过去的变化趋势，还是查看数据分组后的平均变化情况或者是一些总量之类的东西。前者我觉得对Temparal Table的历史表应用聚集索引配合PAGE COMPRESSION，后者对Temparal Table的历史表创建Clustered Columnstore Index，加上以分区表技术（时间字段选择end time）。
参考：
Temporal Tables
Getting Started with System-Versioned Temporal Tables
Temporal Table System Consistency Checks
Partitioning with Temporal Tables
Temporal Table Considerations and Limitations
Manage Retention of Data in History Tab
本文系统来源：http://www.cnblogs.com/lonelyxmas/p/5206172.html
内容总结
以上是互联网集市为您收集整理的SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表）全部内容，希望文章能够帮你解决SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。