在系统版本控制时态表中修改数据

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

系统版本控制时态表中的数据使用常规数据操作语言 (DML) 语句进行修改,但有一个重要的区别:无法直接修改时间段列数据。 数据更新时,它就是版本控制的,每个已更新行的以前版本都将插入到历史记录表中。 当删除数据时,删除是合乎逻辑的,行将从当前表移入历史记录表中;数据不会被永久删除。

插入数据

插入新数据时,如果它们不是 HIDDEN,则需要对 PERIOD 列作出说明。 还可以对时态表使用分区切换。

使用可见的时间段列插入新数据

有如下可见的 INSERT 列用来说明 PERIOD 列时,则可以构建 PERIOD 语句:

如果在 INSERT 语句中指定列列表,则可以省略 PERIOD 列,因为系统将为这些列自动生成值。

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department] (
      [DeptID],
      [DeptName],
      [ManagerID],
      [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

如果确实在 INSERT 语句中的列列表中指定了 PERIOD 列,那么需要指定 DEFAULT 作为它们的值。

INSERT INTO [dbo].[Department] (
   DeptID,
   DeptName,
   ManagerID,
   ParentDeptID,
   ValidFrom,
   ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

如果未在 INSERT 语句中指定列列表,则为 DEFAULT 列指定 PERIOD

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES(12, 'Production', 101, 1, DEFAULT, DEFAULT);

将数据插入到具有 HIDDEN 时间段列的表中

如果将 PERIOD 列指定为 HIDDEN,则无需对 INSERT 语句中的 PERIOD 列作出说明。 此行为保证对从版本控制中受益的表启用系统版本控制时,旧版应用程序将继续工作。

CREATE TABLE [dbo].[CompanyLocation] (
    [LocID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [LocName] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

使用 PARTITION SWITCH 插入数据

如果当前表已分区,可以使用 PARTITION SWITCH 作为将数据加载到空分区或并行加载到多个分区的有效机制。

在具有时态表的 PARTITION SWITCH IN 语句中使用的临时表必须定义 SYSTEM_TIME PERIOD,但它不需要是时态表。 这可确保在数据插入到临时表期间或将 SYSTEM_TIME 时间段添加到预填充的临时表时,执行时态一致性检查。

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2] (
    [DeptID] [int] NOT NULL,
    [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])
) ON [PRIMARY]

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
ADD CONSTRAINT [Staging_Department_Partition2_PK]
PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2]
WITH CHECK ADD CONSTRAINT [chk_staging_Department_partition_2] CHECK (
   [DeptID] > N'100'
   AND [DeptID] <= N'200'
);

ALTER TABLE [dbo].[Staging_Department_Partition2]
CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
    )
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
SWITCH TO [dbo].[Department] PARTITION 2;

如果尝试在没有时间段定义的情况下从表中执行 PARTITION SWITCH,将收到错误消息:

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

更新数据

使用常规的 UPDATE 语句更新当前表中的数据。 可以灾难情况根据历史记录表来更新当前表中的数据。 但是,无法更新 PERIOD 列,且当 SYSTEM_VERSIONING = ON 时不能直接在历史记录表中更新数据。

如果将 SYSTEM_VERSIONING 设置为 OFF,且从当前表和历史记录表中更新行,则系统不会保留更改的历史记录。

更新当前表

在此示例中,对于 ManagerIDDeptID 的每一行,都将更新 10 列。 PERIOD 列不会以任何方式引用。

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

但是,不能更新 PERIOD 列且不能更新历史记录表。 在此示例中,尝试更新 PERIOD 列会生成错误。

UPDATE [dbo].[Department]
SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

语句可能会生成以下错误。

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

从历史记录表更新当前表

可以对当前表使用 UPDATE,以便在过去的特定时间点将实际行状态恢复为有效状态。 将此视为恢复为“上次已知完好的行版本”。 以下示例显示了恢复为截至 2015 年 4 月 25 日的历史记录表中的值,其中 DeptID10

UPDATE Department
SET DeptName = History.DeptName
FROM Department
FOR SYSTEM_TIME AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

删除数据

使用常规的 DELETE 语句删除当前表中的数据。 已删除行的结束时间段列将填充底层事务的开始时间。 SYSTEM_VERSIONINGON 时,不能直接从历史记录表删除行。 如果设置 SYSTEM_VERSIONING = OFF,且从当前表和历史记录表中删除行,则系统不会保留更改的历史记录。

SYSTEM_VERSIONING = ON 时,不支持以下语句:

  • TRUNCATE
  • 用于当前表的 SWITCH PARTITION OUT
  • 用于历史记录表的 SWITCH PARTITION IN

使用 MERGE 在时态表中修改数据

对于 MERGE 列,INSERT 操作具有与 UPDATEPERIOD 语句相同的限制。

CREATE TABLE DepartmentStaging (
    DeptId INT,
    DeptName VARCHAR(50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);