开发者社区> fanr_zh> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server 2016新特性:Temporal Table

简介: 什么是系统版本的Temporal Table 系统版本的Temporal Table是可以保存历史修改数据并且可以简单的指定时间分析的用户表。 这个Temporal Table就是系统版本的Temporal Table因为每行的有效期由系统托管的。
+关注继续查看
什么是系统版本的Temporal Table
系统版本的Temporal Table是可以保存历史修改数据并且可以简单的指定时间分析的用户表。 这个Temporal Table就是系统版本的Temporal Table因为每行的有效期由系统托管的。
每个Temporal Table有2个显示定义的列,类型是datetime2。这些用来表示有效期。这个列用来标记这个行是不是在期间内可用。
除了上面的period列,l临时表也包含了引用到其他表,系统使用这个表来保存行修改删除前的行版本。这个附加表可以认为是history表,主表包含了当前的行版本为当前表。在Temporal Table创建的时候可以指定一个history表或者让系统创建一个默认的history表。
 
临时表的工作原理
系统版本的表是有一对表,当前表和历史表。这些表都包含2个额外的datetime2字段用来定义每个行的可用期限:
  • 期限开始列:系统把行的开始时间记录在这个列上,称为SysStartTime
  • 期限结束列:系统把行的结束时间记录在这个列上,称为SysEndTime
当前表包含了每个行的当前值。历史表包含每个行的之前的只,starttime,endtime表示行的可用期限。
以下是一个例子:
CREATE TABLE dbo.Employee  
(   
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED  
  , [Name] nvarchar(100) NOT NULL 
  , [Position] varchar(100) NOT NULL  
  , [Department] varchar(100) NOT NULL 
  , [Address] nvarchar(1024) NOT NULL 
  , [AnnualSalary] decimal (10,2) NOT NULL 
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START 
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END 
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
 )   
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
可以删除括号中的HISTORY_TABLE系统会自动创建history表。
 
INSERT:对于一个insert,系统会设置SysStartTime列为当前事务的开始时间,SysEndTime为最大的值9999-12-31
UPDATE:对于update,系统会报之前的行保存到历史表并且设置SysEndTime为当前事务的启动时间。行被关闭,这个期限就是这个行的可用期限。这个行在当前表上的值被修改,那么SysStartTime被设置为当前事务的开始时间。SysEndTime被设置为最大时间。
DELETE:对于删除,系统把之前的行保存到history表,并且设置SysEndtime为事务的开始时间。标记行关闭,期限记录表示行的可用期限。当前表中行被删除。当前的查询不会被查到当前行。只有带时间的查询,或者直接查询历史表才能查到这个行。
MERGE:对于MERGE涉及到3个操作INSERT,UPDATE,DELETE,根据操作的不同做不同的记录。
 
临时数据查询
可以使用select from的for system_time子句来查询当前表和历史表的数据。
 
以下是查询的例子:
SELECT * FROM Employee  
    FOR SYSTEM_TIME   
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'  
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;
注意:
FOR SYSTEM_TIME会过滤掉SysStartTime=SysEndTime的数据。这些行在同一个事务里面操作了同一行儿产生。只能通过查询历史表才能返回
 
关于SYSTEM_TIME过滤
表达式 符合条件的行 Description
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time 返回一个表,其行中包含过去指定时间点的实际(当前)值。 在内部,临时表及其历史记录表之间将进行联合,然后筛选结果以返回在 <date_time> 参数指定的时间点有效的行中的值。 如果 system_start_time_column_name 值小于或等于 <date_time> 参数值,并且 system_end_time_column_name 值大于 <date_time> 参数值,则此行的值视为有效。
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_time 返回一个表,其中包含在指定的时间范围内保持活动状态的所有行版本的值,不管这些版本是在 FROM 自变量的 <start_date_time> 参数之前开始活动,还是在 TO 自变量的 <end_date_time> 参数值之后停止活动。 在内部,将在临时表及其历史记录表之间进行联合,然后筛选结果,以返回在指定时间范围内任意时间保持活动状态的所有行版本的值。 正好在 FROM 终结点定义的下限时间停止活动的行将被排除,正好在 TO 终结点定义的上限时间开始活动的记录也将被排除。
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time 与上面的 FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> 描述相同,不过,返回的行表包括在 <end_date_time> 终结点定义的上限时间激活的行。
CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_time 返回一个表,其中包含在 CONTAINED IN 参数的两个日期时间值定义的时间范围内打开和关闭的所有行版本的值。 正好在下限时间激活的记录,或者在上限时间停止活动的行将包括在内。
ALL 所有行 返回属于当前表和历史记录表的行的联合。
注意:
可以通过Hidden隐藏期限列,删除表需要先关闭系统版本 ALTER TABLE Employee SET (SYSTEM_VERSIONING =off )之后才能删除表

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Server 2012 T-SQL 新特性
原文:SQL Server 2012 T-SQL 新特性 序列 Sequence SQL Server 现在将序列当成一个对象来实现,创建一个序列的例子语法如下: CREATE SEQUENCE DemoSequence START WITH 1 INCREMENT BY 1; 使用序列的方法如下所表达的: SELECT VALUE FOR DemoSequence 序列与以前的种子列(identity)的区别很明显,种子列只限于当前列,而序列是一个对象层面的实现,则可以在多个表之间共享。
957 0
SQL Server--获取磁盘空间使用情况
原文:SQL Server--获取磁盘空间使用情况 对于DBA来说,监控磁盘使用情况是必要的工作,然后没有比较简单的方法能获取到磁盘空间使用率信息,下面总结下这些年攒下的脚本: 最常用的查看磁盘剩余空间,这个属于DBA入门必记的东西: -- 查看磁盘可用空间 EXEC master.
790 0
SQL Server 2014新特性:其他
AlwaysOn 增强功能 SQL Server 2014 包含针对 AlwaysOn 故障转移群集实例和 AlwaysOn 可用性组的以下增强功能: “添加 Azure 副本向导”简化了用于 AlwaysOn 可用性组的混合解决方案创建。
1076 0
magento错误 Service Temporarily Unavailable magento
前台访问出现错误 Service Temporarily Unavailable magento 解决方法 Service TemporarilyUnavailable字面意思是此服务暂时无法使用,如果说你对Magento的各项操作已经完成的情况下,仍然出现该提示,那么下面的方法可以帮助你解决问题。
884 0
Java连接sqlserver的程序
<pre name="code" class="java">package com.ytu; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Test { public stati
1134 0
+关注
fanr_zh
数据库技术专家
341
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载