SQL Server 2016新特性: Temporal table

原创 2016年06月02日 08:12:38


     曾经我们遇过,误删(或更新)数据的情况,补救措施通常是还原数据库,然后把数据还原回来,整个过程比较繁琐耗时。SQL Server 2016引入了一个新的功能,即Temporaltable,我们可以称之为历史表,因为它记录了表在历史上任何时间点所做的改动。有了这个功能,一旦发生误操作,我们就可以及时进行数据恢复,很酷的一项功能。

 

Temporal Table要具备几个条件:

  1. 必须有主键。

  2. 必须定义两个数据类型为datetime2的列,用来记录开始和结束时间点。如果需要,可以使用HIDDEN标记隐藏这两个列。这两个列也称为SYSTEM_TIME时间范围列(periodcolumns)。

  3. 历史表必须是和主表在结构上一模一样,包括字段名字和数据类型。

  4. INSTEADOF触发器是不允许的,AFTER触发器仅能用于主表。

  5. 不能用于In-momory OLTP

 

下面一起感受一下该特性:

1.  创建经版本控制的系统(system-versioned)表:

CREATE TABLE dbo.TestTemporal
    (ID int primary key
    ,A int
    ,B int
    ,C AS A * B
    ,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);

如果没有指定历史表的名称,SQL Server会自动生成一个dbo.MSSQL_TemporalHistoryFor_xxx的历史表,其中xxx是主表的object id。历史表创建完成后,长得像下面这个样子,我们可以对历史表进行重命名,比如变成TestTemporal_History。

历史表的列与主表的列完全一样,但所有的约束会被移除。历史表可以有自己的索引和统计信息,在历史表上创建索引,比如clustered columnstore index,能够大幅改善性能。


2.   测试历史表的功能

首先insert几笔数据:

INSERT INTO dbo.TestTemporal(ID, A, B)
VALUES  (1,2,3)
        ,(2,4,5)
        ,(3,0,1);

SELECT * FROM dbo.TestTemporal;
insert成功后,主表的数据如下:

现在对数据进行delete和update操作:

DELETE FROM dbo.TestTemporal
WHERE ID = 2;

UPDATE dbo.TestTemporal
SET A = 5
WHERE ID = 3;

这时来查看主表和历史表的数据,可以看到历史表把主表变化前的数据记录了下来


3.  更改system-versioned表的schema

当一个表启用了system-versioning时,对主表修改,就会有严格的限制。允许做?ALTER TABLE … REBUILD, CREATE INDEX,  CREATE STATISTICS, 但schema修改是不允许的。 下面的示例演示了,drop主表是不允许的。

但是,如果你需要对主表增加列,该怎么办呢?好办,可以依循如下步骤:

首先,禁用system-versioning:

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);


禁用之后,原先的主表和历史表,会变成我们熟知的主表,如下所示:


这时,你就可以对两张表进行修改,不过要确保张表表的一致性,为了演示,我对2张表同时增加了一列D。 修改完成后,可以通过下面的T-SQL启用system-versioning:

ALTER TABLE dbo.TestTemporal ADD PERIOD FOR SYSTEM_TIME([SysStartTime],[SysEndTime])
ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE=dbo.TestTemporal_History,DATA_CONSISTENCY_CHECK=ON)
);
刷新之后,就可以看到当前的样子:刷新之后,就可以看到现在的样子:





SQL Server 2016新特性: Always Encrypted (始终加密)

数据,尤其是机密数据的安全性,是我们设计和开发系统所要考虑的。SQL Server 2016引入了加密数据列的新方式,即始终加密(Always Encrypted)。有了始终加密,数据就可以通过AD...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月02日 08:43
  • 2747

SQL Server 2016 新功能之综述

SQL Server2016 新功能之综述 冬去春来,发现之前最后一篇写在2012年,又过去了5年了,时间如飞啊。那时候SQL 2012 发布让人兴奋了一把,哪知道时间如刀,刀刀催人老啊,今天SQ...
  • capsicum29
  • capsicum29
  • 2017年02月18日 15:10
  • 2585

SQL Server 2016新特性: 对JSON的支持

SQL Server 2005开始支持XML数据类型,提供原生的XML数据类型、XML索引及各种管理或输出XML格式的函数。随着JSON的流行,SQL Server2016开始支持JSON数据类型,...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月01日 17:15
  • 4007

Windows Server 2016新特性比较

  • 2016年08月26日 15:58
  • 1.34MB
  • 下载

迁移数据到历史表SQL

有时工作需要需要把当前表的数据,移到历史表中,而历史表基本是以时间(年)为后缀来命名历史表的,如 A_2011,A_2012,在移数据时,要按数据的时间,移到不同的表中,且由于如果数据有同步。一次处理...
  • otong
  • otong
  • 2012年01月11日 11:14
  • 3311

SQL Server2016各类版本的区别

微软 SQL Server 2016 正式版将分为四个版本,分别是企业版 (Enterprise)、标准版(Standard)、速成版(Express)和开发人员版本(Developer)。其中,和 ...
  • fishlookbird
  • fishlookbird
  • 2017年03月27日 10:17
  • 7525

SQL Server 2016新特性: In-Memory OLTP

内存中OLTP有助于OLTP工作负荷实现显著的性能改进,并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务,并且可以使用Transact-SQL进行访...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月01日 16:57
  • 2551

Sql Server 2016 新功能——内置的 Temporal Tables

放假之前老大跟我提起了一下2016有个有趣的功能叫 Temporal Table ,今天去看了一下资料整理一下。 这个功能看上去像是临时表,但是其实是系统维护的一个历史记录表。(在某个程度上面比起我...
  • dz45693
  • dz45693
  • 2016年10月07日 16:39
  • 639

SQL Server 2016新特性: Temporal table

曾经我们遇过,误删(或更新)数据的情况,补救措施通常是还原数据库,然后把数据还原回来,整个过程比较繁琐耗时。SQL Server 2016引入了一个新的功能,即Temporaltable,我们可以称...
  • Burgess_Liu
  • Burgess_Liu
  • 2016年06月02日 08:12
  • 2012

sql server2016里面的json功能

  • 2017年06月28日 15:50
  • 18KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL Server 2016新特性: Temporal table
举报原因:
原因补充:

(最多只允许输入30个字)