如何在SQL Server中的系统版本化的临时表中查询数据

This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN. Also, we’ll cover how to clean up the history table to keep it a manageable size.

本文将介绍通过使用FOR SYSTEM_TIME子句及其四个子子句AS OF，FROM TO，BETWEEN AND和CONTAINED IN来查询SQL Server中的时态表。 此外，我们还将介绍如何清理历史记录表以使其保持可管理的大小。

The FOR SYSTEM_TIME clause is used to perform any type of time-based analysis combaing with four sub clauses and it can be used for each table in the query independently. Also, it can be used inside table-valued functions and stored procedures.

FOR SYSTEM_TIME子句用于结合四个子子句执行任何类型的基于时间的分析，并且可以独立地用于查询中的每个表。 而且，它可以在表值函数和存储过程中使用。

Before we start, let’s create a SQL Server database with a temporal table dbo.People. To play alone you can copy and execute the following code in the query window:

在开始之前，我们先创建一个带有临时表dbo.PeopleSQL Server数据库。 要单独播放，您可以在查询窗口中复制并执行以下代码：

 
CREATE DATABASE test;
GO
USE test;
GO
CREATE TABLE People( 
PeopleID int PRIMARY KEY NOT NULL, Name varchar(50) Null,
LastName varchar(100) NULL, 
NickName varchar(25) NULL, 
StartTime datetime2 (0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
EndTime datetime2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'), 
PERIOD FOR SYSTEM_TIME (StartTime,EndTime) ) 
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));
 

This will create the system-versioned table dbo.People and the corresponding history table dbo.HistoryPeople:

这将创建系统版本表dbo.People和相应的历史记录表dbo.HistoryPeople：

For more information about SQL Server temporal tables, please visit Temporal tables in SQL Server.

有关SQL Server时态表的更多信息，请访问SQL Server中的时态表 。

Let’s insert and update some data:

让我们插入和更新一些数据：

 
INSERT INTO dbo.People VALUES(2,'James','Smith', 'Jam',DEFAULT, DEFAULT)
 
WAITFOR DELAY '00:01:00'
 
UPDATE dbo.People
SET
   dbo.People.Name = 'Thomas' WHERE dbo.People.PeopleID=2
 
WAITFOR DELAY '00:02:00'
 
INSERT INTO dbo.People VALUES(3,'Joan','Johnson','Jon',DEFAULT, DEFAULT)
 
WAITFOR DELAY '00:01:00'
 
UPDATE dbo.People
SET
   dbo.People.Name = 'Paul' WHERE dbo.People.PeopleID=3
 
WAITFOR DELAY '00:02:00'
 
INSERT INTO dbo.People VALUES (4,'Robert','Davis', 'Rob',DEFAULT, DEFAULT)
 
WAITFOR DELAY '00:01:00'
 
UPDATE dbo.People
SET
   dbo.People.Name = 'Nik' WHERE dbo.People.PeopleID=4
 
WAITFOR DELAY '00:02:00'
 
UPDATE dbo.People
SET
   dbo.People.Name = 'Brian' WHERE dbo.People.PeopleID=2
 
WAITFOR DELAY '00:01:00'
 
UPDATE dbo.People
SET
   dbo.People.Name = 'Mark' WHERE dbo.People.PeopleID=2
 

Both system-versioned and history table can be queried by using standard

可以使用标准查询系统版本表和历史记录表

SELECT * FROM <TableName> query statement.

SELECT * FROM <TableName>查询语句。

查询系统版本的dbo.People表 (Querying system-versioned dbo.People table)

Code:

码：

 
SELECT * FROM dbo.People
 

Result:

结果：

This will return all current (actual) data:

这将返回所有当前（实际）数据：

查询SQL Server历史记录dbo.HistoryPeople表 (Querying SQL Server history dbo.HistoryPeople table)

Code:

代码 ：

 
SELECT * FROM dbo.PeopleHistory
 

Result:

结果：

This will return all data changes (Update, Delete, Merge) that were made in the dbo.People system versioned table:

这将返回在dbo.People系统版本表中进行的所有数据更改（ Update ， Delete ， Merge ）：

ALL子句 (ALL sub clause)

This clause will return all the rows from both the dbo.People system versioned and dbo.PeopleHistory history table.

此子句将从dbo.People系统版本化和dbo.PeopleHistory历史记录表中返回所有行。

Type the following code:

输入以下代码：

 
SELECT *
FROM   dbo.People FOR SYSTEM_TIME ALL;
 

Result:

结果：

The same result can be obtained by using the following query and omit the FOR SYSTEM_TIME clause:

通过使用以下查询并省略FOR SYSTEM_TIME子句，可以获得相同的结果：

 
SELECT *
FROM dbo.People p
UNION ALL
SELECT *
FROM dbo.PeopleHistory ph; 
 

AS OF子条款 (AS OF sub clause)

Using the AS OF sub clause can return a state of the data for each row containing the values that were current at the specified time in the past. The AS OF sub clause returns all the records from the SQL Server system versioned and history table that satisfied the below criteria:

使用AS OF子句可以为每行返回数据状态，其中包含过去指定时间的当前值。 AS OF子句返回满足以下条件SQL Server系统版本和历史记录表中的所有记录：

StartTime <= SpecifiedTime AND EndTime > SpecifiedTime

StartTime <= SpecifiedTime AND EndTime > SpecifiedTime

The query below will return the records which were valid at specific time in the past (‘2017-01-26 13:52:29’) for the specific PeopleID = 2:

以下查询将返回在过去特定时间（'2017-01-26 13:52:29'）对于特定PeopleID = 2有效的记录：

 
SELECT * FROM dbo.People 
FOR SYSTEM_TIME AS OF '2017-01-26 13:52:29'
WHERE PeopleID=2
 

Result:

结果：

The same result can be achieved by typing the following code and omit FOR SYSTEM_TIME AS OF:

通过键入以下代码并省略FOR SYSTEM_TIME AS OF可以达到相同的结果：

 
SELECT* FROM dbo.People p
WHERE p.StartTime <= '2017-01-26 13:52:29'
AND p.EndTime > '2017-01-26 13:52:29'
UNION ALL
SELECT ph.* FROM dbo.PeopleHistory ph
WHERE ph.StartTime <='2017-01-26 13:52:29'
AND ph.EndTime > '2017-01-26 13:52:29'
 

从<开始日期时间>到<结束日期时间>子句 (FROM <start_date_time> TO <end_date_time> sub clause)

This temporal sub clause is useful when you need to get changes for all records that were active between <start_date_time> and <end_date_time>. FROM … TO … sub clause returning the data from both tables system versioned and history.

当需要获取在<start_date_time>和<end_date_time>之间处于活动状态的所有记录的更改时，此时间子句很有用。 FROM…TO…子句从系统版本表和历史记录表中返回数据。

The following criteria must be fulfilled:

必须满足以下条件：

StartTime < <end_date_time> AND EndTime > <start_date_time>

开始时间<<结束日期时间> AND 结束时间 > <开始日期时间>

Execute the following code:

执行以下代码：

 
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29'
DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29'   
              
SELECT *
FROM dbo.People
FOR SYSTEM_TIME FROM @StartDate TO @EndDate
WHERE dbo.People.PeopleID=2
 

Result:

结果：

The same result can be achieved by typing the following code, without using FOR SYSTEM_TIME FROM… TO…

通过键入以下代码，无需使用FOR SYSTEM_TIME FROM…TO… ，即可获得相同的结果。

 
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29'
DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' 
 
SELECT * FROM dbo.People p
WHERE p.StartTime < @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2
UNION ALL
SELECT * FROM dbo.PeopleHistory ph
WHERE ph.StartTime < @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2
 

BETWEEN <开始日期时间>和<结束日期时间>子句 (BETWEEN <start_date_time> AND <end_date_time> sub clause)

This is almost the same as the FROM … TO … sub clause, except that BETWEEN … TO … sub clause includes the records that were active on <end_date_time> (i.e. StartTime = <end_date_time>). The BETWEEN … TO … sub clause returns all the row that satisfied the following criteria:

这与FROM…TO…子句几乎相同，除了BETWEEN…TO…子句包括在<end_date_time>上处于活动状态的记录（即StartTime = <end_date_time>）。 BETWEEN…TO…子句返回满足以下条件的所有行：

StartTime <= <end_date_time> AND EndTime > <start_date_time>

开始时间<= <结束日期时间>和结束时间 > <开始日期时间>

Execute the following code:

执行以下代码：

 
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29'
DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' 
 
SELECT *
FROM dbo.People FOR SYSTEM_TIME BETWEEN @StartDate AND @EndDate
WHERE dbo.People.PeopleID=2
 

Result:

结果：

The same result can be achieved by using this code, without using BETWEEN … TO … sub clause:

通过使用此代码，而无需使用BETWEEN…TO…子句，可以达到相同的结果：

 
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29'
DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' 
 
 
SELECT * FROM dbo.People p
WHERE p.StartTime <= @EndDate AND p.EndTime > @StartDate AND p.PeopleID=2
UNION ALL
SELECT * FROM dbo.PeopleHistory ph
WHERE ph.StartTime <= @EndDate AND ph.EndTime > @StartDate AND ph.PeopleID=2
 

包含在（<开始日期时间>，<结束日期时间>）子句中 (CONTAINED IN(<start_date_time>, <end_date_time>) sub clause )

This temporal sub- clause will return only the records that existed within the specified period boundaries. The CONTAINED IN sub clause returns all the row that satisfied the following criteria:

该时间子句将仅返回指定期间边界内存在的记录。 CONTAINED IN子句返回满足以下条件的所有行：

StartTime >= <start_date_time> AND EndTime <= <end_date_time>

开始时间> = <开始日期时间>和结束时间<= <结束日期时间>

Execute the following code:

执行以下代码：

 
DECLARE @StartDate DATETIME2 = '2017-01-26 13:52:29'
DECLARE @EndDate DATETIME2 = '2017-01-26 14:01:29' 
 
SELECT * FROM dbo.People FOR SYSTEM_TIME CONTAINED IN (@StartDate,@EndDate) WHERE dbo.People.PeopleID=2
 

Result:

结果：

The same result can be achieved using this code:

使用以下代码可以达到相同的结果：

 
SELECT * FROM dbo.People p
WHERE p.StartTime >= @StartDate AND p.EndTime <= @EndDate AND p.PeopleID=2
UNION ALL
SELECT * FROM dbo.PeopleHistory ph
WHERE ph.StartTime >= @StartDate AND ph.EndTime <= @EndDate AND ph.PeopleID=2
 

清理SQL Server历史记录表 (Cleaning up the SQL Server history table)

Over time the history table can grow significantly. Since inserting, updating or deleting data from the history table are not allowed, the only way to clean up the history table is first to disable system versioning:

随着时间的推移，历史记录表会显着增长。 由于不允许在历史记录表中插入，更新或删除数据，因此清除历史记录表的唯一方法是首先禁用系统版本控制 ：

 
ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = OFF);
GO
 

Delete unnecessary data from the history table:

从历史记录表中删除不必要的数据：

 
DELETE FROM dbo.PeopleHistory
WHERE EndTime <= '2017-01-26 14:00:29';
 

and then re-enable system versioning:

然后重新启用系统版本控制：

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

Cleaning the history table in Azure SQL Databases is a little different, since Azure SQL databases have built-in support for cleaning of the history table. First, temporal history retention cleanup need to be enable on a database level:

清理 Azure SQL数据库中的历史表有些不同，因为Azure SQL数据库具有对清理历史表的内置支持。 首先，需要在数据库级别启用时间历史记录保留清除：

 
ALTER DATABASE CURRENT
SET TEMPORAL_HISTORY_RETENTION ON
GO
 

Then set the retention period per table.:

然后设置每个表的保留期。：

 
ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 DAYS));
 

This will delete all data in the history table older than 90 days.

这将删除历史记录表中超过90天的所有数据。

SQL Server 2016 on-premise databases do not support TEMPORAL_HISTORY_RETENTION and HISTORY_RETENTION_PERIOD and either of the above two queries are executed on the SQL Server 2016 on-premise databases the following errors will occur:

SQL Server 2016内部部署数据库不支持TEMPORAL_HISTORY_RETENTION和HISTORY_RETENTION_PERIOD，并且以上两个查询之一在SQL Server 2016内部部署数据库上执行，将发生以下错误：

For TEMPORAL_HISTORY_RETENTION error will be:

对于TEMPORAL_HISTORY_RETENTION错误将是：

Msg 102, Level 15, State 6, Line 34
Incorrect syntax near ‘TEMPORAL_HISTORY_RETENTION’.

Msg 102，第15级，状态6，第34行
“ TEMPORAL_HISTORY_RETENTION”附近的语法不正确。

For HISTORY_RETENTION_PERIOD error will be:

对于HISTORY_RETENTION_PERIOD，错误将是：

Msg 102, Level 15, State 1, Line 39
Incorrect syntax near ‘HISTORY_RETENTION_PERIOD’.

消息102，第15层，州1，第39行
“ HISTORY_RETENTION_PERIOD”附近的语法不正确。

翻译自: https://www.sqlshack.com/how-to-query-data-in-a-system-versioned-temporal-tables-in-sql-server/