时间戳

首页 > 解决方案 > 如何使用子句将系统版本控制添加到此表定义中？

sql-server - 如何使用子句将系统版本控制添加到此表定义中？

问题描述

WITH我有下表定义，我想向其中添加系统版本控制，但如果没有 SQL Server 抱怨，我似乎无法将其放入子句中。

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL,
    [SysStartTime] [datetime2](7) NOT NULL,
    [SysEndTime] [datetime2](7) NOT NULL,
 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));

标签： sql-server

解决方案

创建表然后添加所需的系统版本控制元素可能是最简单和最简洁的：

create table [dbo].[Employee](
  [EmployeeId] [int] identity(1,1) not null,
  [FirstName] [varchar](50) null,
  [LastName] [varchar](50) null
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]

alter table dbo.Employee add
    SysStartTime datetime2 generated always as row start constraint DF_EmployeeSysStart default SysUtcDateTime(),
    SysEndTime datetime2 generated always as row end constraint DF_EmployeeSysEnd default Convert(datetime2, '9999-12-31 23:59:59.9999999'),
period for system_time (SysStartTime,SysEndTime);

alter table Employee set (system_versioning = on (history_table = dbo.EmployeeHistory, data_consistency_check=on));

编辑

组合成一个create table

create table [dbo].[Employee](
  [EmployeeId] [int] identity(1,1) not null,
  [FirstName] [varchar](50) null,
  [LastName] [varchar](50) null,
    [SysStartTime] datetime2 generated always as row start constraint DF_EmployeeSysStart default SysUtcDateTime(),
    [SysEndTime] datetime2 generated always as row end hidden constraint DF_EmployeeSysEnd default Convert(datetime2, '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)
) with ( system_versioning = on (history_table = dbo.EmployeeHistory, data_consistency_check=on))

推荐阅读