创建系统版本控制时态表

  • 项目

适用于： 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_TIMEdatetime2 列的主键，声明为 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 压缩，否则将取消压缩历史记录表。 例如，某些表配置（如稀疏列）不允许压缩。

  • 系统会为历史记录表创建一个默认聚集索引，该表的名称是自动生成的，其格式为 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+ 树）索引。 创建此用户定义的历史记录表后，将通过将用户定义的历史记录表指定为默认历史记录表，创建由系统控制版本的临时表。

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 精度必须与基础表的精度一致。 请参阅以下备注。

备注

  • 将具有默认值的不可为 null 列添加到包含数据的现有表中是除SQL Server Enterprise版本之外的所有版本的数据操作大小， (元数据操作) 。 对于包含 SQL Server Standard 版本数据的大型现有历史记录表，添加非 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_VERSIONINGDATA_CONSISTENCY_CHECK = ON ，以对现有数据强制实施数据一致性检查。
  • 如果首选隐藏列，请使用命令 ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;。

后续步骤