通过

创建系统版本控制时态表

  • 项目

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

如果考虑到指定历史记录表的方式，有三种方法可创建系统版本控制的时态表：

  • 使用匿名历史记录表创建时态表：指定当前表的架构，让系统使用自动生成的名称创建相应的历史记录表。

  • 使用默认历史记录表创建时态表：指定历史记录表架构名称和表名称，让系统在该架构中创建历史记录表。

  • 使用事先创建的用户定义的历史记录表创建时态表：创建最适合你需求的历史记录表，然后在时态表创建过程中引用该表。

使用匿名历史记录表创建时态表

使用匿名历史记录表创建时态表是快速创建对象的便捷选择，特别是在原型环境和测试环境中。 它也是创建时态表最简单的方法，因为不需要在 SYSTEM_VERSIONING 子句中指定任何参数。 下面的示例将在不定义历史记录表名称的情况下，创建启用了系统版本控制的新表。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

注解

系统版本控制的时态表必须已定义主键，并且已定义且只定义了一个 PERIOD FOR SYSTEM_TIME（其中包含两个 datetime2 列，声明为 GENERATED ALWAYS AS ROW START 或 GENERATED ALWAYS AS ROW END）。

PERIOD 列始终不可为 null，即使未指定是否为 null，也是如此。 如果将 PERIOD 列显式定义为可为 null，则 CREATE TABLE 语句将失败。

历史记录表必须在列数、列名、排序和数据类型方面始终与当前表或时态表保持架构对齐。

将在当前表或临时表所在的架构中自动创建匿名历史记录表。

匿名历史记录表名称采用以下格式：MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>。 后缀是可选的，仅当表名称的第一部分不唯一时才会添加。

历史记录表将创建为行存储表。 如果可能，将应用 PAGE 压缩，否则历史记录表将不会进行压缩。 例如，某些表配置（如 SPARSE 列）不允许压缩。

系统会为历史记录表创建一个默认聚集索引，该表的名称是自动生成的，其格式为 IX_<history_table_name>。 聚集索引包含 PERIOD 列（结束、开始）。

要将当前表创建为内存优化表，请参阅系统版本控制时态表与内存优化表。

使用默认历史记录表创建时态表

如果想要控制命名并仍依赖于系统创建具有默认配置的历史记录表，使用默认历史记录表创建时态表是一个便捷选择。 在下面的示例中，将在显式定义历史记录表的名称的情况下，创建启用了系统版本控制的新表。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

注解

将使用适用于创建“匿名”历史记录表的相同规则来创建历史记录表，而以下规则专门适用于命名的历史记录表。

  • 对于 HISTORY_TABLE 参数，架构名称是必需的。

  • 如果指定的架构不存在，CREATE TABLE 语句将失败。

  • 如果 HISTORY_TABLE 参数指定的表已存在，则将根据新创建的时态表，就架构一致性和时态数据一致性对其进行验证。 如果指定的历史记录表无效，CREATE TABLE 语句将失败。

使用用户定义的历史记录表创建时态表

如果用户想要指定具有特定存储选项和针对历史查询优化的其他索引的历史记录表，则使用用户定义的历史记录表创建时态表是一个便捷选择。 在下面的示例中，将使用与要创建的临时表一致的架构创建用户定义的历史记录表。 对于此用户定义的历史记录表，系统会为点查找创建一个聚集列存储索引和一个额外的非聚集行存储（B- 树）索引。 创建此用户定义的历史记录表后，将用户定义的历史记录表指定为默认历史记录表来创建时态表。

注意

文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，数据库引擎实现了 B+ 树。 这不适用于列存储索引或内存优化表上的索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

注解

如果打算对历史数据运行使用聚合或窗口函数的分析查询，则强烈建议创建聚集列存储作为主索引，因为这样会有非常好的数据压缩和查询性能。

如果打算将时态表用于数据审核（即从当前表中搜索单个行的历史更改），则应创建具有聚集索引的行存储历史记录表。

历史记录表不能具有主键、外键、唯一索引、表约束或触发器。 不能将其配置为进行变更数据捕获、更改跟踪、事务复制或合并复制。

将非时态表更改为由系统控制版本的时态表

你可以在现有的非时态表上启用系统版本控制，例如当你希望将自定义的时态解决方案迁移到内置支持时。

例如，可能有一组表使用触发器实现了版本控制。 使用时间系统版本控制并不太复杂并提供了其他好处，包括：

  • 不可变的历史记录
  • 用于按时间顺序查询的新语法
  • 更好的 DML 性能
  • 最低的维护成本

转换现有表时，建议使用 HIDDEN 子句来隐藏新的 PERIOD 列（datetime2 列 ValidFrom 和 ValidTo），以免影响不显式指定列名称（例如，不含列列表的 SELECT * 或 INSERT）且不旨在处理新列的现有应用程序。

将版本控制添加到非临时表

如果你想要开始跟踪包含数据的非临时表的更改，则需要添加 PERIOD 定义，并可以选择为 SQL Server 为你创建的空历史记录表提供名称：

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

重要

DATETIME2 的精度必须与基础表的精度一致。

注解

对 SQL Server Enterprise Edition 以外的所有版本而言，将具有默认值的非 null 列添加到一张包含数据的现有表是一种关于数据大小的操作（对 Enterprise Edition 是一种元数据操作）。 对于 SQL Server Standard Edition 中包含数据的大型现有历史记录表而言，添加非 null 列代价高昂。

必须小心选择期间开始和期间结束列的约束：

  • 开始列的默认值指定从哪个时间点起应考虑现有行有效。 不能将它指定为将来的日期时间点。

  • 结束时间必须指定为一个给定 datetime2 精度的最大值，例如 9999-12-31 23:59:59 或 9999-12-31 23:59:59.9999999。

添加 PERIOD 将对当前表执行数据一致性检查，以确保期间列的现有值有效。

如果在启用 SYSTEM_VERSIONING 时指定了现有历史记录表，则将对当前表和历史记录表同时执行临时数据一致性检查。 如果将 DATA_CONSISTENCY_CHECK = OFF 指定为额外参数，则可跳过此项检查。

将现有表迁移到内置支持

此示例演示如何基于触发器将现有解决方案迁移到内置临时支持。 对于此示例，我们假定当前自定义解决方案将当前数据和历史数据拆分为两个单独的用户表（ProjectTaskCurrent 和 ProjectTaskHistory）。

如果现有解决方案使用单个表来存储实际行和历史行，则应在执行以下示例所示的迁移步骤前将数据拆分为两个表。 首先，将触发器放在未来的临时表上。 然后，确保 PERIOD 列不可为 null。

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

注解

引用 PERIOD 定义中的现有列会将这些列的 generated_always_type 隐式更改为 AS_ROW_START 和 AS_ROW_END。

添加 PERIOD 将对当前表执行数据一致性检查，以确保期间列的现有值有效。

强烈建议将 SYSTEM_VERSIONING 与 DATA_CONSISTENCY_CHECK = ON 一起设置，以便对现有数据执行数据一致性检查。

如果首选隐藏列，请使用命令 ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;。

相关内容