Tabelas temporais

Aplica-se a:yes SQL Server 2016 (13.x) e posterior YesBanco de Dados SQL do Azure YesInstância Gerenciada de SQL do Azure

Tabelas temporais (também conhecidas como tabelas temporais com controle da versão do sistema) são um recurso de banco de dados com suporte interno para fornecer informações sobre dados armazenados na tabela em qualquer ponto no tempo em vez de apenas os dados que estão corretos no momento.

Você pode começar com Introdução às tabelas temporais com controle da versão do sistema e examinar os Cenários de uso de tabela temporal.

O que é uma tabela temporal com versão do sistema?

Uma tabela temporal com controle da versão do sistema é um tipo de tabela de usuário criada para manter um histórico completo de alterações de dados, permitindo uma análise fácil de pontos no tempo. Esse tipo de tabela temporal é conhecido como tabela temporal com versão do sistema porque o período de validade para cada linha é gerenciado pelo sistema (ou seja, o mecanismo de banco de dados).

Cada tabela temporal tem duas colunas explicitamente definidas, cada um com um tipo de dados datetime2 . Essas colunas são chamadas de colunas de período. Essas colunas de período são usadas exclusivamente pelo sistema para registrar o período de validade para cada linha sempre que uma linha é modificada. Essa tabela principal que armazena os dados atuais é conhecida como a tabela atual ou simplesmente como a tabela temporal.

Além dessas colunas de período, uma tabela temporal também contém uma referência a outra tabela com um esquema espelhado, chamada de tabela de histórico. O sistema usa a tabela de histórico para armazenar a versão anterior da linha automaticamente sempre que uma linha na tabela temporal é atualizada ou excluída. Durante a criação da tabela temporal, os usuários podem especificar uma tabela de histórico existente (deve ser um esquema em conformidade) ou deixar que o sistema crie a tabela de histórico padrão.

Por que temporal?

As fontes de dados reais são dinâmicas e geralmente as decisões comerciais dependem das informações que os analistas podem obter da evolução dos dados. Os casos de uso de tabelas temporais incluem:

  • Auditar todas as alterações de dados e executar análise forense de dados quando necessário
  • Reconstruir o estado dos dados a partir de qualquer momento no passado
  • Calcular tendências ao longo do tempo
  • Manter uma dimensão de alteração lenta para aplicações de suporte a decisões
  • Recuperar alterações acidentais em dados e erros de aplicativos

Como funciona a tabela temporal?

A versão do sistema para uma tabela é implementada como um par de tabelas, uma tabela atual e uma tabela de histórico. Dentro de cada uma dessas tabelas, duas colunas datetime2 adicionais são usadas para definir o período de validade para cada linha:

  • Coluna de início do período: O sistema registra a hora de início da linha na coluna, normalmente denotada como a coluna ValidFrom.
  • Coluna de término do período: O sistema registra a hora de término da linha na coluna, normalmente indicada como a coluna ValidTo.

A tabela atual contém o valor atual para cada linha. A tabela de histórico contém cada valor anterior (a versão antiga) para cada linha, se houver, e a hora de início e término do período para o qual ela era válida.

Diagram showing how a Temporal table works

O seguinte script ilustra um cenário com informações de funcionários:

CREATE TABLE dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Para obter mais informações, consulte Criar uma tabela temporal com controle da versão do sistema.

  • INSERTS: o sistema define o valor para a coluna ValidFrom como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema e atribui o valor para a coluna ValidTo para o valor máximo de 9999-12-31. Isso marca a linha como aberta.
  • UPDATES: o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna ValidTo como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema. Isso marca a linha como fechada, com um período registrado para o qual a linha era válida. Na tabela atual, o registro é atualizado com o novo valor e o sistema define o valor para a coluna ValidFrom para a hora de início da transação (no fuso horário UTC) com base no relógio do sistema. O valor de registro atualizado na tabela atual para a coluna ValidTo permanece o valor máximo de 9999-12-31.
  • DELETES: o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna ValidTo como a hora de início da transação atual (no fuso horário UTC) com base no relógio do sistema. Isso marca a linha como fechada, com um período registrado para o qual a linha anterior era válida. Na tabela atual, a linha é removida. As consultas da tabela atual não retornarão essa linha. Somente as consultas que lidam com dados de histórico retornarão dados cujo linha está fechada.
  • MERGE: a operação se comporta exatamente como se até três instruções (uma INSERT, uma UPDATE e/ou uma DELETE) fossem executadas, dependendo do que é especificado como ações na instrução MERGE.

Importante

As horas registradas nas colunas datetime2 do sistema baseiam-se na hora de início da própria transação. Por exemplo, todas as linhas inseridas em uma única transação terão o mesmo horário UTC registrado na coluna correspondente ao início do período SYSTEM_TIME .

Como faço para consultar dados temporais?

A cláusula FROM<table> da instrução SELECT tem uma nova cláusula FOR SYSTEM_TIME com cinco subcláusulas temporais específicas para consultar dados das tabelas atual e histórica. Essa nova sintaxe de instrução SELECT é suportada diretamente em uma única tabela, propagada por meio de várias associações e exibições em várias tabelas temporais.

Quando você consulta usando a cláusula FOR SYSTEM_TIME usando uma das cinco subcláusulas, os dados históricos da tabela temporal serão incluídos, conforme mostrado na imagem a seguir.

Diagram showing how Temporal Querying works

A seguinte consulta procura por versões de linha para um funcionário, com a condição de filtro WHERE EmployeeID = 1000, que estavam ativas durante, pelo menos, parte do período entre 1º de janeiro de 2021 e 1º de janeiro de 2022 (incluindo o limite superior):

SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
      WHERE EmployeeID = 1000 ORDER BY ValidFrom;

Observação

FOR SYSTEM_TIME filtra as linhas que têm um período de validade com duração zero (ValidFrom = ValidTo).

Essas linhas serão geradas se você realizar várias atualizações na mesma chave primária na mesma transação. Nesse caso, a consulta temporal retorna somente versões de linha anteriores às transações e as linhas atuais após as transações.

Se você precisa incluir as linhas na análise, veja diretamente a tabela de histórico.

Na tabela a seguir, ValidFrom na coluna Linhas Qualificadas representa o valor na coluna ValidFrom da tabela que está sendo consultada e ValidTo representa o valor da coluna ValidTo da tabela que está sendo consultada. Para obter a sintaxe completa e, por exemplo, consulte FROM (Transact-SQL) e Consultar dados em uma tabela temporal System-Versioned.

Expression Linhas de qualificação Observação
A PARTIR DEDATE_TIME ValidFrom<= date_time AND ValidTo>date_time Retorna uma tabela com linhas que contêm os valores que foram atuais no momento especificado no passado. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores na linha que era válida no ponto no tempo especificado pelo parâmetro date_time. O valor de uma linha é considerado válido se o valor de system_start_time_column_name é menor ou igual ao valor do parâmetro date_time e o valor de system_end_time_column_name é maior que o valor do parâmetro date_time.
FROMstart_date_timeTOend_date_time ValidFrom<end_date_time E ValidTo>start_date_time Retorna uma tabela com os valores para todas as versões de linha que estavam ativas no intervalo de tempo especificado, não importando se eles começaram a ser ativos antes do valor de parâmetro start_date_time para o argumento FROM ou deixaram de ser ativos após o valor de parâmetro end_date_time para o argumento TO. Internamente, uma união é executada entre a tabela temporal e sua tabela de histórico e os resultados são filtrados para retornar os valores para todas as versões de linha que estavam ativas a qualquer momento durante o intervalo de tempo especificado. As linhas que deixaram de ser ativas exatamente no limite inferior definido pelo ponto de extremidade FROM não são incluídas e os registros que se tornaram ativos exatamente no limite superior definido pelo ponto de extremidade TO também não são incluídos.
BETWEENstart_date_timeANDend_date_time ValidFrom<= end_date_time AND ValidTo>start_date_time O mesmo que acima na descrição for SYSTEM_TIME FROMstart_date_timeTOend_date_time, exceto que a tabela de linhas retornadas inclui linhas que se tornaram ativas no limite superior definido pelo ponto de extremidade end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom>= start_date_time AND ValidTo<= end_date_time Retorna uma tabela com os valores para todas as versões de linha que foram abertas e fechadas dentro do intervalo de tempo especificado definido por dois valores de período para o argumento CONTAINED IN. As linhas que se tornaram ativas exatamente no limite inferior ou que deixaram de ser ativas exatamente no limite superior são incluídas.
ALL Todas as linhas Retorna a união de linhas que pertencem às tabelas atual e de histórico.

Ocultar as colunas de período

Você pode optar por ocultar as colunas de período, de modo que as consultas que não fazem referência explícita a elas não retornam essas colunas (por exemplo, ao executar SELECT * FROM <table> ).

Para retornar uma coluna oculta, basta fazer referência explícita à coluna oculta na consulta. Da mesma forma, as instruções INSERT e BULK INSERT continuarão como se as novas colunas de período não estivessem presentes (e os valores da coluna serão populados automaticamente).

Para obter detalhes sobre como usar a cláusula HIDDEN, veja CREATE TABLE e ALTER TABLE.

Exemplos

ASP.NET

Consulte este aplicativo Web do ASP.NET Core para saber como criar um aplicativo temporal usando tabelas temporais.

Baixar o banco de dados de exemplo do Adventure Works

Você pode baixar o banco de dados do AdventureWorks para SQL Server, que inclui recursos de tabela temporal.

Confira também

Próximas etapas