查询系统版本控制的临时表中的数据

  • 项目

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

如果想要获取时态表中数据的最新（当前）状态，可以像查询非时态表一样进行查询。 PERIOD如果未隐藏列，则其值将显示在查询中SELECT *。 如果将列指定 PERIOD 为 HIDDEN，则其值不会显示在查询中 SELECT * 。 当 PERIOD 列隐藏时，必须在 SELECT 子句中明确引用 PERIOD 列以返回这些列的值。

若要执行任何一种基于时间的分析，请将新的 FOR SYSTEM_TIME 子句和四个特定于时态表的子子句结合使用，以便跨当前表和历史记录表查询数据。 有关这些子句的详细信息，请参阅临时表和 FROM (Transact-SQL)

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

FOR SYSTEM_TIME可以在查询中为每个表单独指定 。 它可以在公用表表达式、表值函数和存储过程内使用。 将表别名与临时表结合使用时， FOR SYSTEM_TIME 必须在临时表名称和别名之间包括 子句 (请参阅 使用 AS OF 子 句第二个示例) 查询特定时间。

使用 AS OF 子子句查询特定时间

当你因为数据处于过去的任意特定时间而需要重新构造数据状态时，可使用 AS OF 子子句。 你可以使用 PERIOD 列定义中指定的 datetime2 类型的精度来重新构造数据。

子 AS OF 函数可以与常量文本或变量一起使用，以便可以动态指定时间条件。 所提供的值解释为 UTC 时间。

第一个示例返回 dbo.Department 表从过去某个特定日期起的状态。

/*State of entire table AS OF specific date in the past*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

第二个示例对行子集两个时间点之间的值进行比较。

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(day, -1, sysutcdatetime());

/*Comparison between two points in time for subset of rows*/
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

在时态查询中将视图与子句一起使用AS OF

在需要进行复杂时间点分析的情况下，使用视图非常有用。 常见的示例是使用上个月的值生成现在的业务报表。

通常情况下，客户会有一个规范化的数据库模型，该模型涉及多个具有外键关系的表。 如何让该规范化模型中的数据看上去像处于过去的某个时间点，要查明此问题可能非常难，因为所有表都按自己的频率独立进行更改。

在这种情况下，最好的选择是创建一个视图并将 AS OF 子子句应用于整个视图。 使用此方法可将数据访问层的建模与时间点分析分离，因为SQL Server以透明方式将子句应用于AS OF参与视图定义的所有临时表。 此外，可以将临时表与非时态表合并在同一视图中，并且 AS OF 仅应用于临时表。 如果视图未引用至少一个时态表，则向其应用时态查询子句会失败并出现错误。

以下示例代码创建一个联接三个时态表的视图： Department、 CompanyLocation和 LocationDepartments：

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

现在可以使用子句和 datetime2 文本查询视图AS OF：

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF'2021-09-01 T10:00:00.7230011';

或者，可以使用具有本地时区 和 AT TIME ZONE的AS OF子句查询视图：

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

查询一段时间内特定行的更改

如果需要获取当前表中特定行的所有历史更改（也称为数据审核），时态子子句 FROM ... TO、BETWEEN ... AND 和 CONTAINED IN 将非常有用。

前两个子子句返回与指定时间段重叠的行版本（即，在给定时间段之前启动并在其之后结束的行版本），CONTAINED IN 则仅返回指定时间段边界内存在的行版本。

如果仅搜索非当前行版本，则应直接查询历史记录表，以获得最佳查询性能。 如果需要在没有任何限制的情况下查询当前数据和历史数据，则使用 ALL。

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

后续步骤