如何为Employee表定义正确添加系统版本化WITH子句配置

常见报错的核心触发原因 #

• 语法位置错误：原配置把系统版本化的WITH子句和索引参数的WITH块混放，索引的WITH仅用于配置索引的存储、锁行为等参数，不支持配置表级的系统版本化属性。
• 缺少强制前置配置：未声明PERIOD FOR SYSTEM_TIME，SQL Server无法识别哪两个列是用于记录行版本时间范围的系统列。
• 列校验不通过：两个时间列未设置符合规范的默认值，无法满足系统版本化的列校验规则。

方案1：建表时一次性完成系统版本化配置（适用于未创建表的场景） #

完整可运行的建表脚本如下：

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SysStartTime] [datetime2](7) NOT NULL DEFAULT SYSUTCDATETIME(),
    [SysEndTime] [datetime2](7) NOT NULL DEFAULT '9999-12-31 23:59:59.9999999',
    -- 必须声明系统时间周期，否则系统无法识别版本化列
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]),
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- 表级系统版本化配置独立放在表定义最外层，与索引的WITH块完全分离
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[EmployeeHistory], DATA_CONSISTENCY_CHECK=ON));

方案2：已存在表的分步开启配置（适用于已按初始建表语句创建Employee表的场景） #

无需删表重建，按顺序执行以下语句即可：

1. 为两个时间列补充符合要求的默认约束

ALTER TABLE [dbo].[Employee] 
    ADD CONSTRAINT DF_Employee_SysStartTime DEFAULT SYSUTCDATETIME() FOR [SysStartTime],
    CONSTRAINT DF_Employee_SysEndTime DEFAULT '9999-12-31 23:59:59.9999999' FOR [SysEndTime];

2. 声明系统时间周期列

ALTER TABLE [dbo].[Employee]
    ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

3. 正式开启系统版本化，指定历史表参数

ALTER TABLE [dbo].[Employee]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[EmployeeHistory], DATA_CONSISTENCY_CHECK=ON));

配置注意事项 #

• 历史表无需手动预先创建，开启系统版本化时SQL Server会自动按当前表结构创建匹配的历史表。
• 启用DATA_CONSISTENCY_CHECK=ON会在开启时校验现有行的时间范围逻辑，避免脏数据导致版本化查询异常。
• 表的聚集索引必须包含主键列，当前的主键配置满足该要求，不会触发校验报错。

内容的提问来源于stack exchange，提问作者Legion