SQL Server 2016新特性： Temporal table

最新推荐文章于 2024-01-15 17:42:46 发布

Burgess_Liu

最新推荐文章于 2024-01-15 17:42:46 发布

阅读量4.5k

点赞数

分类专栏： SQL Server 2016新特性 SQL SERVER 文章标签： Temporal table

本文链接：https://blog.youkuaiyun.com/Burgess_Liu/article/details/51557520

版权

SQL SERVER 同时被 2 个专栏收录

92 篇文章

订阅专栏

SQL Server 2016新特性

10 篇文章

订阅专栏

SQL Server 2016引入Temporal Table，提供历史数据记录，便于误操作后的数据恢复。创建Temporal Table需满足主键、datetime2时间戳列等条件，历史表与主表结构相同。通过示例展示了Temporal Table的创建、使用及schema更改过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

Temporal Table要具备几个条件：

必须有主键。
必须定义两个数据类型为datetime2的列，用来记录开始和结束时间点。如果需要，可以使用HIDDEN标记隐藏这两个列。这两个列也称为SYSTEM_TIME时间范围列（periodcolumns）。
历史表必须是和主表在结构上一模一样，包括字段名字和数据类型。
INSTEADOF触发器是不允许的，AFTER触发器仅能用于主表。
不能用于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)
);

刷新之后，就可以看到当前的样子：刷新之后，就可以看到现在的样子：