Consultar dados em uma tabela temporal com controle da versão do sistema

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Quando quiser obter o estado mais recente (atual) dos dados em uma tabela temporal, você pode fazer uma consulta da mesma maneira 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 as colunas PERIOD como HIDDEN, seus valores não aparecerão em uma consulta SELECT *. Quando as colunas PERIOD estiverem ocultas, faça referência às colunas PERIOD especificamente na cláusula SELECT para retornar os valores para essas colunas.

Para executar qualquer tipo de análise baseada em tempo, use a nova cláusula FOR SYSTEM_TIME com quatro subcláusulas específicas temporais para consultar dados entre as tabelas atuais 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

É possível especificar FOR SYSTEM_TIME independentemente para cada tabela em uma consulta. Ela pode ser usada 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 (veja o segundo exemplo em Consultar um horário específico usando a subcláusula AS OF, abaixo).

Consultar um horário específico usando a subcláusula AS OF

Use a subcláusula AS OF quando você precisar reconstruir o estado dos dados como eram em qualquer momento específico no passado. Você pode reconstruir os dados com a precisão do tipo datetime2 que foi especificado nas definições da 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 AS OF de 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;

Usar exibições com a subcláusula AS OF em consultas temporais

Usar exibições é útil em cenários quando há necessidade de análise pontual complexa. Um exemplo comum é gerar um relatório de negócios hoje com os valores do mês anterior.

Geralmente, 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 eram em um ponto no passado pode ser bastante desafiador, uma vez que todas as tabelas são alteradas de modo independente, em seu próprio ritmo.

Nesse caso, a melhor opção é criar uma exibição e aplicar a subcláusula AS OF em toda a exibição. Usar essa abordagem permite separar a modelagem da camada de acesso aos dados da análise pontual, pois o SQL Server aplica a cláusula AS OF de modo transparente a todas as tabelas temporais que participam da definição da exibição. Além disso, você pode combinar tabelas temporais e tabelas não temporais na mesma exibição, e AS OF será aplicado apenas às tabelas temporais. Se a exibição não referenciar pelo menos uma tabela temporal, haverá a aplicação de cláusulas de consulta temporais gerará um erro.

O código de exemplo a seguir cria uma exibição que une três tabelas temporais: Department, CompanyLocation e 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 literal datetime2:

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

Consultar 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ê precisar obter todas as alterações de histórico de uma linha específica na tabela atual (também conhecido como uma auditoria de dados).

As duas primeiras subcláusulas retornam versões de linha que sobrepõem um período específico (isto é, aquelas que começaram antes de determinado período e foram encerradas depois dele), enquanto CONTAINED IN retorna apenas aquelas que existiram dentro de limites de período específicos.

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

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