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