通过

临时表

  • 项目

适用于：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

时态表（也称为系统版本控制时态表）以数据库功能的形式引入了内置支持，可以提供表中存储的数据在任意时间点的相关信息，而不仅仅是数据在当前时刻正确的信息。

系统版本控制的时态表入门，并查看时态表使用方案。

什么是版本由系统控制的临时表？

系统版本控制的时态表是用户表的一种类型，旨在保留完整的数据更改历史记录，并轻松实现时间点分析。 这种类型的临时表之所以称为版本由系统控制的临时表，是因为每一行（即数据库引擎）的有效期由系统管理。

每个临时表有两个显式定义的列，其中每个列都有一个 datetime2 数据类型。 这些列称为期限列。 每当修改了某行后，系统将以独占方式使用这些期限列来记录每行的有效期。 存储当前数据的主表称为当前表，或简称为时态表 。

除了这些时间段列以外，时态表还包含对使用镜像架构的另一个表（成为历史记录表）的引用。 每当更新或删除了时态表中的某行后，系统将使用历史记录表来自动存储该行的先前版本。 在创建时态表期间，可以指定现有的历史记录表（必须与架构相符），或者让系统创建默认的历史记录表。

为何使用临时表？

实际的数据源是动态的，业务决策多半依赖于分析师从数据演变中获得的见解。 临时表的用例包括：

  • 在必要时审核所有数据变更并执行数据取证
  • 重构数据在过去任意时间之前的状态
  • 计算各时间段的趋势
  • 为决策支持应用程序保持一个慢速变化的维度
  • 在发生意外的数据更改和应用程序错误后进行恢复

临时表的工作原理是什么？

表的系统版本控制是以一对表（当前表和历史记录表）的形式实现的。 在其中每个表中，两个额外的 datetime2 列用于定义每行的有效时间段：

  • 时间段开始时间列：系统在此列（通常表示为 ValidFrom 列）中记录行的开始时间。

  • 时间段结束时间列：系统在此列（通常表示为 ValidTo 列）中记录行的结束时间。

当前表包含每个行的当前值。 历史记录表包含每个行的每个先前值（旧版本）（如果有），以及该行生效的开始时间和结束时间。

显示时态表工作方式的示意图。

以下脚本演示了包含员工信息的场景：

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

有关详细信息，请参阅创建系统版本控制的时态表。

  • 插入：系统基于系统时钟将 ValidFrom 列的值设置为当前事务的开始时间（采用 UTC 时间），并将 ValidTo 列的值指定为最大值 9999-12-31。 这会将行标记为已打开。

  • 更新：系统将行的先前值存储在历史记录表中，并基于系统时钟将 ValidTo 列的值设置为当前事务的开始时间（采用 UTC 时间）。 这会将行标记为已关闭，并记录该行有效的期限。 在当前表中，将使用新值更新行，同时，系统会基于系统时钟将 ValidFrom 列的值设置为事务的开始时间（采用 UTC 时间）。 在当前表中，ValidTo 列的更新行值将为最大值 9999-12-31。

  • 删除：系统将行的先前值存储在历史记录表中，并基于系统时钟将 ValidTo 列的值设置为当前事务的开始时间（采用 UTC 时间）。 这会将行标记为已关闭，并记录前一行有效的期限。 在当前表中，该行将被删除。 对当前表的查询不会返回此行。 处理历史记录数据的查询将返回已关闭的行的数据。

  • 合并：根据 INSERT 语句中被指定为操作的内容，该操作的行为与最多执行了三个语句（UPDATE、DELETE 和/或 MERGE）完全一样。

系统 datetime2 列中记录的时间基于事务本身的开始时间。 例如，在单个事务中插入的所有行在此列具有相同的 UTC 时间，对应 SYSTEM_TIME 时间段的开始时间。

在时态表上运行任何数据修改查询时，即使没有列值更改，数据库引擎也会向历史记录表添加一行。

如何查询临时数据？

SELECT ... FROM <table> 语句提供新的子句 FOR SYSTEM_TIME 和五个特定时态的子子句，用于跨当前表和历史记录表查询数据。 支持对通过多个联接传播的，以及通过多个时态表顶层的视图传播的单个表直接使用这种新的 SELECT 语句语法。

通过使用五个子子句之一的 FOR SYSTEM_TIME 子句进行查询时，将包括时态表中的历史数据，如下图所示。

显示临时查询工作方式的示意图。

以下查询将使用 WHERE EmployeeID = 1000 且至少在 2021 年 1 月 1 日至 2022 年 1 月 1 日的某段时间（包括上限）内保持活动状态的筛选条件来搜索员工的行版本：

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME 会筛选出有效期持续时间为零 (ValidFrom = ValidTo) 的行。

如果对同一事务中的同一个主键执行多项更新，将生成这些行。 在这种情况下，时态查询只会返回发生事务之前的行版本，以及发生事务之后的当前行。

如果需要在分析中包括这些行，请直接查询历史记录表。

在下表中，“符合条件的行”列中的 ValidFrom 表示正在查询的表中 ValidFrom 列的值，ValidTo 表示正在查询的表中 ValidTo 列的值。 如需了解完整语法和示例，请参阅 FROM 子句，以及 JOIN、APPLY 和 PIVOT 和在系统版本控制时态表中查询数据。

表达式 符合条件的行 注意
AS OF date_time ValidFrom <= date_time AND ValidTo > date_time 返回一个表，其行中包含过去指定时间点的当前值。 在内部，将在时态表与其历史记录表之间进行联合。 筛选结果来返回在 date_time 参数指定的时间点有效的行中的值。 如果 system_start_time_column_name 值小于或等于 date_time 参数值，并且 system_end_time_column_name 值大于 date_time 参数值，则此行的值视为有效。
FROM start_date_time TO end_date_time ValidFrom < end_date_time AND ValidTo > start_date_time 返回一个表，其中包含在指定的时间范围内保持活动状态的所有行版本的值，不管这些版本是在 FROM 自变量的 start_date_time 参数值之前开始活动，还是在 TO 自变量的 end_date_time 参数值之后停止活动。 在内部，将在时态表与其历史记录表之间进行联合。 筛选结果，以返回在指定时间范围内任意时间保持活动状态的所有行版本的值。 正好在 FROM 终结点定义的下限时间停止活动状态的行将被排除，正好在 TO 终结点定义的上限时间开始活动状态的记录也将被排除。
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time 与上面的 FOR SYSTEM_TIME FROM start_date_time TO end_date_time 描述相同，不过，返回的行表会包括在 end_date_time 终结点定义的上限时间变为活动状态的行。
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_time AND ValidTo <= end_date_time 返回一个表，其中包含在 CONTAINED IN 自变量的两个时间段值定义的指定时间范围内打开和关闭的所有行版本的值。 正好在下限时间激活的记录，或者在上限时间停止活动的行将包括在内。
ALL 所有行 返回属于当前表和历史记录表的行的联合。

隐藏时间段列

可以选择隐藏时间段列，这样未显式引用它们的查询就不会返回这些列（例如，在运行 SELECT * FROM <table> 时）。

若要返回隐藏的列，必须在查询中显式引用隐藏的列。 同样，如果这些新的时间段列不存在，INSERT 和 BULK INSERT 语句将会继续（并且列值将自动填充）。

若要了解如何使用 HIDDEN 子句，请参阅 CREATE TABLE 和 ALTER TABLE。

示例

相关内容