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

han8809

　　作为SQL Server 2016(CTP3.x)的另一个新特性，Temporal Table（历史表）记录了表历史上任何时间点所有的数据改动。Temporal Table其实早在ANSI SQL 2011就提出了，而SAP HANA, DB2和Oracle早已在它们的产品中加入/实现了这一特性。所以说微软其实是落后了几个竞争对手。既然在CTP3.0中加入了，相信RTM也肯定有这个特性。
　　Temporal Table（历史表）有何作用？
　　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_TIMEALL, 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.TemporalTableTEST5CREATE TABLE dbo.TemporalTableTEST5　　
(
　　
     ID INT PRIMARY KEY CLUSTERED)GOSELECT * FROM dbo.TemporalTableTEST5GOALTER TABLE dbo.TemporalTableTEST5ADD 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)GOALTER TABLE dbo.TemporalTableTEST5SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableTEST5_History, DATA_CONSISTENCY_CHECK = ON));GO

　　如果表中有数据，需要分开来完成这个转换过程。

--DROP TABLE dbo.TemporalTableTEST4CREATE TABLE dbo.TemporalTableTEST4　　
(
　　
     ID INT PRIMARY KEY CLUSTERED)GOINSERT INTO dbo.TemporalTableTEST4(ID)VALUES(1),(2),(3)GO--INSERT INTO dbo.TemporalTableTEST4(ID)--VALUES(4),(5),(6)--GOALTER TABLE dbo.TemporalTableTEST4ADD 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')GOSELECT * FROM dbo.TemporalTableTEST4GO--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 ENDALTER TABLE dbo.TemporalTableTEST4ADD PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);GOALTER TABLE dbo.TemporalTableTEST4SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TemporalTableTEST4_History, DATA_CONSISTENCY_CHECK = ON));GO

　　如果你直接用第一种办法就会收到一个错误提示
Msg 13575, Level 16, State 0, Line 53ADD 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);GOCREATE PARTITION SCHEME myPS1AS PARTITION myPFTO ( [primary], [primary], [primary], [primary] );--DROP TABLE dbo.TemporalTableTEST7_HistoryCREATE 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倒是做得挺好的，就是不需要你改完主表还要去改历史表。比如你添加一个字段和一个默认约束到主表，历史表也自动应用到同样的改动。

  Name ()    DK_TemporalTableTEST4_Name　　select * from dbo.TemporalTableTEST4
　　select * from dbo.TemporalTableTEST4_History

　　结果

　　某些情况下我们可以不停用SYSTEM_VERSIONING的情况下照样完成了对主表的结构改动，比如添加一个正常的字段（非compted等），但是如果添加诸如identity或者computed字段则需要停用system_versioning。否则
Msg 13724, Level 16, State 1, Line 135System-versioned table schema modification failed because adding computed column while system-versioning is ON is not supported.　　但是有一点是例外，就是如果你要删除主表的一个字段，除了要删除主表字段上创建的约束外还要删除历史表上对应字段的约束，否则
Msg 5074, Level 16, State 1, Line 142The object 'DF__TemporalTa__dttm__02FC7413' is dependent on column 'dttm'.　　
Msg 4922, Level 16, State 9, Line 142ALTER 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.TemporalTableTEST5ADD float_col FLOATGOINSERT INTO dbo.TemporalTableTEST5(ID, float_col)VALUES(1,100),(2,200),(3,300)GOUPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50WHERE ID = 1UPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50WHERE ID = 1UPDATE dbo.TemporalTableTEST5 SET float_col = float_col + 50WHERE ID = 1

　　查询全部的历史数据
SELECT ID, float_col, [SysStartTime],[SysEndTime] FROM [dbo].TemporalTableTEST5　　
FOR SYSTEM_TIME ALL

ID    float_col    SysStartTime    SysEndTime1    250    2016-02-21 09:27:52.0379197    9999-12-31 23:59:59.99999992    200    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999993    300    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999991    100    2016-02-21 09:27:38.6363057    2016-02-21 09:27:40.51624461    150    2016-02-21 09:27:40.5162446    2016-02-21 09:27:46.43125591    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    SysEndTime2    200    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999993    300    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999991    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    SysEndTime1    250    2016-02-21 09:27:52.0379197    9999-12-31 23:59:59.99999992    200    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999993    300    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999991    150    2016-02-21 09:27:40.5162446    2016-02-21 09:27:46.43125591    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    SysEndTime2    200    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999993    300    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999991    100    2016-02-21 09:27:38.6363057    2016-02-21 09:27:40.5162446　　第二个返回了

ID    float_col    SysStartTime    SysEndTime1    250    2016-02-21 09:27:52.0379197    9999-12-31 23:59:59.99999992    200    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999993    300    2016-02-21 09:27:38.6363057    9999-12-31 23:59:59.99999991    150    2016-02-21 09:27:40.5162446    2016-02-21 09:27:46.43125591    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    SysEndTime1    150    2016-02-21 09:27:40.5162446    2016-02-21 09:27:46.43125591    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）。

ying0904peng

了解一下