SQL Server 2016新功能--Temporal Table--快速恢复误操作
来源:互联网 发布:中央网络问政平台 编辑:程序博客网 时间:2024/06/08 19:14
Temporal Table是 SQL Server 2016的一个新特性,我们可以称之为历史记录表,它能记录表中任何时间点所做的数据改动。有了这个功能,就能够在发生误操作的情况下及时对数据进行恢复。
先简单介绍一下这个新特性,启用Temporal Table功能后表结构如下图所示:
可以看到,启用Temporal Table 后,在主表中多了一张历史记录表,历史表中就记录了所有的数据改动。而且表中还多了2个字段,SysTimeStart和 SysTimeEnd,这2个字段类型都是datetime2,多出来的这2个字段记录了数据发生变动的时间。
向表写入数据时如下所示:
SysTimeStart和SysTimeEnd 2个字段是由SQL Server自动写入的,记录了数据的操作时间,后面会详细介绍。可以看到,SysTimeStart的时间和写入数据的时间不一样,是因为SysTimeStart和SysTimeEnd所使用的是UTC格式的时间。UTC+时区=本地时间,所以SysTimeStart的时间与本地时间要差8小时。
接下来我们详细介绍 Temporal Table功能
使用Temporal Table功能的条件:
1,必须要有主键。
2,必须定义两个数据类型为datetime2的列,用来记录开始和结束时间点,如上面图片中的SysTimeStart 和SysTimeEnd 。且字段不能为NULL。
3,历史表必须和主表的结构一摸一样,包括字段名字和数据类型。
使用下面的语句创建有 Temporal Table 功能的表
CREATE TABLE Test_TemporalTable(ID INT IDENTITY(1,1) PRIMARY KEY ,Names VARCHAR(10) , SysTimeStart DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL ,SysTimeEnd DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL ,PERIOD FOR SYSTEM_TIME(SysTimeStart,SysTimeEnd)) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTableHistroy)) ;
创建完成后,如下图所示:
如果没有指定历史记录表的名称,SQL Server将自动生成一个dbo.MSSQL_TemporalHistoryFor_xxx的历史表,其中xxx是主表的object id,自动生成的表名效果如下图所示:
使用历史表功能
首先向主表中写入一些数据
INSERT INTO Test_TemporalTable (Names) VALUES ('Tom'),('Jakey'),('张三'),('李四')
写入数据后,我们查询主表和历史记录表,可以看到,历史记录表中并没有数据。且主表中SysTimeStart 和SysTimeEnd 由SQL Server自动填上数据,后面将会说明这两个字段的意义。
现在来修改和删除表中的数据
UPDATE Test_TemporalTable SET Names = '王五' WHERE ID = 1 DELETE Test_TemporalTable WHERE ID = 2
再查询两张表,就会看到,历史记录表中已经把操作的数据记录了下来,并且记录下了操作的时间(Histroy表中的SysTimeEnd字段)
简单说明一下:
Temporal Table功能其实是对两张数据库表进行了数据版本化(System-versioning)。一张是主表,一张是主表的历史记录表。Temporal Table的条件之一是添加两个类型为datetime2的字段来标识记录数据的时间范围 ( SysTimeStart和SysTimeEnd)。这两个字段是由SQL Server自动进行维护的,可以在建表的时候对字段加入HIDDEN关键字把字段隐藏,这样就避免两个字段在SELECT * FROM或者INSERT INTO的时候出现在列表里面。
当写入(insert)时,写入的时间会被写入到主表的SysTimeStart字段中,SysTimeEnd 则被记录为'9999-12-31 23:59:59.9999999',历史记录表不会有任何记录。
当更新(update)发生时,历史记录表中的SysTimeStart会记录原数据写入的时间,SysTimeEnd会记录本次更新的时间,主表的SysTimeStart则被更新为本次更新的时间,SysEndTime依旧还是'9999-12-31 23:59:59.9999999'。
当删除(delete)发生时,历史记录表中的SysTimeStart会记录原数据写入的时间,SysTimeEnd会记录本次删除操作的时间
如果一行数据再次发生Update操作,则在历史记录表中会再生产一行记录,将原表的值以及操作时间记录下来。
有了历史记录表中的数据,我们就能在发生误操作的时候,及时的将数据恢复,再不用还原备份才能恢复数据了,而且还可以根据时间来恢复数据。
有了这个新功能后,怎样将现有的表转换成Temporal Table的表呢?
要将现有表转换成Temporal Table功能的表,分2种情况,一种是现有的表中没有数据,是空表,一种是现有的表中已有数据,非空表。
下面来分情况转换
1,将空表转换成Temporal Table
/*创建一张空表,将空表转换成Temporal Table*/CREATE TABLE dbo.Test_TemporalTable_New( ID INT IDENTITY(1,1) PRIMARY KEY , Names VARCHAR(10))GOSELECT * FROM dbo.Test_TemporalTable_NewGO---将空表转换成Temporal TableALTER TABLE dbo.Test_TemporalTable_NewADD SysTimeStart DATETIME2 GENERATED ALWAYS AS ROW STARTCONSTRAINT DF_Test_TemporalTable_New_SysStart DEFAULT SYSUTCDATETIME() ,SysTimeEnd DATETIME2 GENERATED ALWAYS AS ROW ENDCONSTRAINT DF_Test_TemporalTable_New_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59.9999999'),PERIOD FOR SYSTEM_TIME (SysTimeStart, SysTimeEnd)GOALTER TABLE dbo.Test_TemporalTable_NewSET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTable_New_History, DATA_CONSISTENCY_CHECK = ON));GO
代码中 DATA_CONSISTENCY_CHECK 只是检查 SysTimeStart 是否小于等于 SysTimeEnd
转换后的效果如下图所示:
2,将有数据的表转换成 Temporal Table 有数据的表转换比空表转换多出一个步骤。
/*将有数据的表转换成Temporal Table,一步一步做*/ALTER TABLE dbo.Test_TemporalTable_DataADD SysTimeStart DATETIME2 NOT NULL CONSTRAINT DF_Test_TemporalTable_Data_SysStart DEFAULT SYSUTCDATETIME() , SysTimeEnd DATETIME2 NOT NULL CONSTRAINT DF_Test_TemporalTable_Data_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999')GOALTER TABLE dbo.Test_TemporalTable_DataADD PERIOD FOR SYSTEM_TIME (SysTimeStart, SysTimeEnd);GOALTER TABLE dbo.Test_TemporalTable_DataSET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTable_Data_History, DATA_CONSISTENCY_CHECK = ON));GO
- SQL Server 2016新功能--Temporal Table--快速恢复误操作
- SQL Server 2016新特性: Temporal table
- Sql Server 2016 新功能——内置的 Temporal Tables
- SQL Server 2016 新功能之综述
- SQL Server 2005新功能
- SQL Server 2005新功能
- SQL SERVER 2005 新功能
- SQL Server 2008新功能
- SQL Server 2012 新功能
- SQL Server 2008 数据库误操作后的数据恢复
- SQL SERVER回滚恢复误操作的数据
- sql server 常用数据库恢复操作
- SQL Server数据库恢复操作实例
- sql server 常用数据库恢复操作
- SQL Server 2005新功能 - TSQL
- SQL Server 2005新功能(1)
- SQL Server 2005新功能(1)
- SQL Server 2005新功能- TSQL
- Curator工具类之ZKPaths。
- 如何用命令修改oracle的用户的用户名
- java.lang.IllegalStateException:app is in background uid UidRecord
- ShaderWeaver使用教程-基本操作介绍
- c#进行http请求
- SQL Server 2016新功能--Temporal Table--快速恢复误操作
- SDUT-3376 数据结构实验之查找四:二分查找
- FFmpeg之AVFrame
- linux命令
- hibernate
- 高可用Hadoop平台-Hue In Hadoop
- pycaffe使用
- hive数据倾斜问题
- PHP AJAX JSONP实现跨域请求