Partilhar via

Interrogar dados numa tabela temporal versionada pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL Database AzureSQL Managed InstanceSQL database in Microsoft Fabric

Quando quiser obter o estado mais recente (atual) dos dados em uma tabela temporal, você pode consultar da mesma forma que consulta uma tabela não temporal. Se as colunas PERIOD não estiverem ocultas, seus valores aparecerão em uma consulta SELECT *. Se você especificou PERIOD colunas como HIDDEN, seus valores não aparecerão em uma consulta SELECT *. Quando as colunas PERIOD estão ocultas, você deve fazer referência às colunas PERIOD especificamente na cláusula SELECT para retornar os valores dessas colunas.

Para executar qualquer tipo de análise baseada no tempo, use a nova cláusula FOR SYSTEM_TIME com quatro subcláusulas específicas do tempo para consultar dados nas tabelas atual e de histórico. Para obter mais informações sobre essas cláusulas, consulte Tabelas temporais e cláusula FROM mais JOIN, APPLY, PIVOT

  • 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 pode ser especificado independentemente para cada tabela em uma consulta. Ele pode ser usado dentro de expressões de tabela comuns, funções com valor de tabela e procedimentos armazenados. Ao usar um alias de tabela com uma tabela temporal, a cláusula FOR SYSTEM_TIME deve ser incluída entre o nome da tabela temporal e o alias (consulte Consulta para um tempo específico usando a subcláusula AS OF segundo exemplo).

Consultar por um tempo específico usando a subcláusula AS OF

Use a subcláusula AS OF quando precisar reconstruir o estado dos dados como era em qualquer momento específico no passado. Você pode reconstruir os dados com a precisão do tipo datetime2 especificado nas definições de coluna PERIOD.

A subcláusula AS OF pode ser usada com literais constantes ou com variáveis, para que você possa especificar dinamicamente a condição de tempo. Os valores fornecidos são interpretados como hora UTC.

Este primeiro exemplo retorna o estado da tabela dbo.Department a uma data específica no passado.

-- 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';

Este segundo exemplo compara os valores entre dois pontos no tempo para um subconjunto de linhas.

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;

Utilizar vistas com a subcláusula AS OF em consultas temporais

O uso de visões é útil em cenários onde uma análise complexa num determinado momento é necessária. Um exemplo comum é gerar um relatório de negócios hoje com os valores do mês anterior.

Normalmente, os clientes têm um modelo de banco de dados normalizado, que envolve muitas tabelas com relações de chave estrangeira. Descobrir como os dados desse modelo normalizado se apresentavam num ponto no passado pode ser um desafio, já que todas as tabelas mudam independentemente ao seu próprio ritmo.

Nesse caso, a melhor opção é criar uma visualização e aplicar a subcláusula AS OF a toda a visualização. O uso dessa abordagem permite dissociar a modelagem da camada de acesso a dados da análise point-in-time, já que o SQL Server aplica AS OF cláusula de forma transparente a todas as tabelas temporais que participam da definição de exibição. Além disso, você pode combinar tabelas temporais com não temporais na mesma exibição e AS OF é aplicada apenas às temporais. Se a vista não fizer referência a pelo menos uma tabela temporal, a aplicação de cláusulas de consulta temporal resultará num erro.

O código de exemplo a seguir cria um modo de exibição que une três tabelas temporais: Department, CompanyLocatione 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

Você pode consultar a exibição usando a subcláusula AS OF e um datetime2 literal:

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

Consulta de alterações em linhas específicas ao longo do tempo

As subcláusulas temporais FROM ... TO, BETWEEN ... AND e CONTAINED IN são úteis quando você precisa obter todas as alterações históricas para uma linha específica na tabela atual (também conhecida como auditoria de dados).

As duas primeiras subcláusulas retornam versões de linha que se sobrepõem a um período especificado (ou seja, aquelas que começaram antes de determinado período e terminaram depois dele), enquanto CONTAINED IN retorna apenas aquelas que existiam dentro de limites de período especificados.

Se você pesquisar apenas versões de linha não atuais, deverá consultar a tabela de histórico diretamente, para obter o melhor desempenho da consulta. Use ALL quando precisar consultar dados atuais e históricos sem restrições.

/* 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;

Conteúdo relacionado

  • Last updated on