SQL Server 2016：时态表

微软 数据库 SQL 时态表

摘要

术语“时态数据（temporal data）”是指那些在数据库中有版本的记录。任何给定的逻辑记录都有一个当前版本和零个或多个先前版本。SQL Server 2016的新特性时态表使得操作时态数据更简单。

正文

术语“时态数据（temporal data）”是指那些在数据库中有版本的记录。任何给定的逻辑记录都有一个当前版本和零个或多个先前版本。当前版本和任意先前版本在数据库中都以物理行的形式存在，虽然未必在同一张表中。

使用时态表时要努力保证数据完整性。每次更新一个行，都需要有一种方法可以确保行的当前版本复制到存储先前版本的表中。这可以通过触发器或存储过程实现，但两种方法都有各自的问题。

同样，查询时态数据也是个挑战。虽然开发人员很容易获取一条逻辑记录的当前版本，但查询特定数据的版本，需要一个复杂而又容易出错的查询。这经常导致开发人员寄希望于专门为这种负载类型而设计的数据库。

SQL Server 2016提供了另外一种选择——新的时态表对象。表面上看，时态表看起来跟普通表一样。它支持大多数列类型、普通索引、列存储索引、外键等等。CRUD类的操作同使用普通SQL或对象关系映射一样。实际上，大多数普通表都可以转换成时态表，而不需要修改使用上述表的存储过程和应用程序。

从实现上来说，时态表实际上是两张表。一张表包含当前值，另一张表管理数据的历史版本。两张表链接在一起，普通表的任何UPDATE或DELETE操作都会自动创建一个相应的历史行。（INSERT操作不会创建历史记录。）

访问历史数据

开发人员可以直接查询历史表，但由于它不包含当前值，所以不会经常用到它。相反，应该总是使用下面的其中一种操作查询基表：

• 时间点：AS OF <date_time>
• 开区间：FROM <start_date_time> TO <end_date_time>
• 左闭右开：BETWEEN <start_date_time> AND <end_date_time>
• 闭区间：CONTAINED IN (<start_date_time> , <end_date_time>)

比如，如果想知道ID为27的客户在第一年中哪个值是活跃的，可以使用查询：

… FROM Customer FOR SYSTEM_TIME AS OF '2015-1-1' WHERE CustomerID = 27

如果换个需求，想查看客户记录在那天的每个版本，可以使用查询：

… FROM Customer FOR SYSTEM_TIME BETWEEN '2015-1-1' AND '2015-1-2'WHERE CustomerID = 27

设计原则

• 时态表需要有一个SysStartTime列和一个SysEndTime列，两个列均为非空DateTime2类型。这些列可以随意命名，由SQL Server管理；用户不能插入或更新这些列的值。
• 不支持FILESTREAM列类型，因为它在数据库之外存储数据。
• 对于表Foo，历史表的默认表名为“FooHistory”。该名可以覆写。
• 历史表不能直接修改，只能通过更新或删除当前表的数据增加它的记录。
• 不支持INSTEAD OF触发器，AFTER触发器只能用在当前表上。

索引必须手动启用。关于这一点，微软给出了一些建议：

为了获得最优的存储大小和性能，一个最优的索引策略是，在当前表上创建一个聚簇列存储索引和/或一个B树行存储索引，在历史表上创建一个聚簇列存储索引。如果创建/使用自己的历史表，那么我们强烈建议创建一个包含当前表主键和时间列的索引，以便提升时态数据查询的速度，以及数据一致性检查操作中一部分查询的速度。如果历史表是行存储的，那么我们建议创建一个聚簇行存储索引。在默认情况下，历史表上会创建一个聚簇行存储索引。至少，我们建议创建一个非聚簇行存储索引。

模式修改

用户不能修改时态表的模式。不过，可以在ALTER TABLE语句中使用SET (SYSTEM_VERSIONING = OFF)将时态表转换成普通表。

这样做完之后，就可以修改这两张表，然后使用SET (SYSTEM_VERSIONING = ON)将它们重新转换成时态表。注意，该语句需要包含历史表的表名和两个系统时间列。

更正：本文的上一个版本曾错误地将FOR SYSTEM_TIME表达式描述为WHERE子句的一部分，而实际上，它是FROM子句的一部分。