SQL Server 2016新功能--Temporal Table--快速恢复误操作

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)
)
GO

SELECT * FROM dbo.Test_TemporalTable_New

GO

---将空表转换成Temporal Table
ALTER TABLE dbo.Test_TemporalTable_New
ADD SysTimeStart DATETIME2 GENERATED ALWAYS AS ROW START
CONSTRAINT DF_Test_TemporalTable_New_SysStart DEFAULT SYSUTCDATETIME() ,
SysTimeEnd DATETIME2 GENERATED ALWAYS AS ROW END
CONSTRAINT DF_Test_TemporalTable_New_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (SysTimeStart, SysTimeEnd)
GO

ALTER TABLE dbo.Test_TemporalTable_New
SET ( 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_Data
ADD 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')

GO

ALTER TABLE dbo.Test_TemporalTable_Data
ADD PERIOD FOR SYSTEM_TIME (SysTimeStart, SysTimeEnd);
GO

ALTER TABLE dbo.Test_TemporalTable_Data
SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Test_TemporalTable_Data_History, DATA_CONSISTENCY_CHECK = ON));
GO

转换后的效果如下图所示：