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

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstâ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 PERIOD colunas não estiverem ocultas, seus valores aparecerão em uma SELECT * consulta. Se você especificou PERIOD colunas como HIDDEN, seus valores não aparecem em uma SELECT * consulta. 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 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 pode ser especificada 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 FOR SYSTEM_TIME cláusula deve ser incluída entre o nome da tabela temporal e o alias (consulte Consulta para um horário específico usando o AS OF segundo exemplo de subcláusula ).

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 AS OF subcláusula 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 AS OF subcláusula 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 a dados da análise pontual, pois SQL Server aplica a cláusula de forma transparente a todas as tabelas AS OF 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 somente às temporais. Se a exibição não fizer referência a pelo menos uma tabela temporal, a aplicação de cláusulas de consulta temporal a ela falhará com um erro.

O código de exemplo a seguir cria uma 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

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

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

Como alternativa, você pode consultar a exibição usando a AS OF subcláusula com um fuso horário local e AT TIME ZONE:

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

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 você pesquisar apenas versões de linha não atuais, 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;

Próximas etapas