如何在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>查询语句。