Tabelas temporais

Aplica-se a: simSQL Server 2016 (13.x) e posterior SimBanco de Dados SQL do Azure SimInstância Gerenciada do Azure SQL

O SQL Server 2016 introduziu o suporte para tabelas temporais (também conhecidas como tabelas temporais com versão do sistema) como um recurso de banco de dados que oferece suporte interno para fornecer informações sobre os dados armazenados na tabela em qualquer ponto no tempo, em vez de apenas os dados que estão corretos atualmente. Temporal é um recurso de banco de dados que foi introduzido no ANSI SQL 2011.

Início rápido

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

Uma tabela temporal com versão do sistema é um tipo de tabela de usuário criada para manter um histórico completo de alterações de dados e permitir 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 gravar o período de validade para cada linha sempre que uma linha é modificada.

Além dessas colunas de período, uma tabela temporal também contém uma referência a outra tabela com um esquema espelhado. O sistema usa essa tabela para armazenar a versão anterior da linha automaticamente sempre que uma linha na tabela temporal é atualizada ou excluída. Essa tabela adicional é conhecida como a tabela de histórico, enquanto a tabela principal que armazena as versões atuais (reais) das linhas é conhecida como a tabela atual ou simplesmente como a tabela temporal. Durante a criação da tabela temporal, os usuários podem especificar uma tabela de histórico existente (deve ter um esquema compatível) 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, as seguintes colunas datetime2 adicionais são usadas para definir o período de validade para cada registro:

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

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

Diagrama que mostra como funciona a Tabela temporal.

O seguinte exemplo simples ilustra um cenário com as Informações do funcionário em um banco de dados de RH hipotético:

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));
  • INSERTS: em uma INSERT, o sistema define o valor para a coluna SysStartTime (no exemplo, é chamada de 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 SysEndTime (no exemplo, é chamada de ValidTo) como o valor máximo de 9999-12-31. Isso marca a linha como aberta.
  • UPDATES: Em uma UPDATE, o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna SysEndTime 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 SysStartTime 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 SysEndTime permanece o valor máximo de 9999-12-31.
  • DELETES: Em uma DELETE, o sistema armazena o valor anterior do registro na tabela de histórico e define o valor para a coluna SysEndTime 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: Em uma 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 instrução SELECT da cláusula FROM <table> tem uma nova cláusula FOR SYSTEM_TIME com cinco subcláusulas específicas temporais para consultar dados das tabelas atual e de histórico. 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.

Diagrama que mostra como funciona a Consulta Temporal.

A consulta a seguir procura por versões de linha da linha Employee com EmployeeID = 1000 que estavam ativas durante, pelo menos, parte do período entre 1º de janeiro de 2014 e 1º de janeiro de 2015 (incluindo o limite superior):

SELECT * FROM Employee
  FOR SYSTEM_TIME
    BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-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 (SysStartTime = SysEndTime). 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 exibe somente versões de linha anteriores às transações e que se tornaram reais 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, SysStartTime na coluna Qualifying Rows representa o valor na coluna SysStartTime da tabela que está sendo consultada e SysEndTime representa o valor da coluna SysEndTime da tabela que está sendo consultada. Para obter a sintaxe completa e exemplos, veja FROM (Transact-SQL) e Consultando dados em uma tabela temporal com controle da versão do sistema.

Expression Linhas de qualificação Descrição
AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_time Retorna uma tabela com uma linha que contém os valores que foram reais (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> .
FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > 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.
BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_time A mesma descrição acima para FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> é válida, exceto que a tabela de linhas retornada 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>) SysStartTime >= start_date_time AND SysEndTime <= 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 data e hora 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.

Observação

Opcionalmente, você pode optar por ocultar essas colunas de período, de modo que as consultas que não fazem referência explícita a essas colunas não as retornarão (o cenário 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 (Transact-SQL) e ALTER TABLE (Transact-SQL).

Próximas etapas