在系统版本控制时态表中修改数据
适用于: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
,且从当前表和历史记录表中更新行,则系统不会保留更改的历史记录。
更新当前表
在此示例中,对于 ManagerID
为 DeptID
的每一行,都将更新 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 日的历史记录表中的值,其中 DeptID
为 10
。
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_VERSIONING
为 ON
时,不能直接从历史记录表删除行。 如果设置 SYSTEM_VERSIONING = OFF
,且从当前表和历史记录表中删除行,则系统不会保留更改的历史记录。
SYSTEM_VERSIONING = ON
时,不支持以下语句:
TRUNCATE
- 用于当前表的
SWITCH PARTITION OUT
- 用于历史记录表的
SWITCH PARTITION IN
使用 MERGE 在时态表中修改数据
对于 MERGE
列,INSERT
操作具有与 UPDATE
和 PERIOD
语句相同的限制。
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);