Compartilhar via

Tabelas temporais

  • Artigo

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

Tabelas temporais (também conhecidas como tabelas temporais com controle de 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.

Comece a usar tabelas temporais com controle de versão pelo sistema e analise os Cenários de uso de tabelas temporais.

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 controle de versão do sistema porque o sistema (ou seja, o mecanismo de banco de dados) é que gerencia o período de validade de cada linha.

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, você pode 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. 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?

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

  • Coluna de início do período: o sistema registra o horário de início para a linha nesta coluna, normalmente denotada como a coluna ValidFrom.

  • Coluna de término do período: o sistema registra o horário de término para a linha nesta coluna, normalmente denotada 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.

Diagrama que mostra como funciona a tabela temporal.

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.

  • Inserções: 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 como o valor máximo de 9999-12-31. Isso marca a linha como aberta.

  • Atualizações: o sistema armazena o valor anterior da linha 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, a linha é atualizada com seu novo valor e o sistema define o valor para a coluna ValidFrom como a hora de início da transação (no fuso horário UTC) com base no relógio do sistema. O valor para a linha atualizada na tabela atual para a coluna ValidTo permanece o valor máximo de 9999-12-31.

  • Exclusões: o sistema armazena o valor anterior da linha 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 retornam esta linha. Somente as consultas que lidam com dados de histórico retornarão dados cujo linha está fechada.

  • Mesclar: a operação se comporta exatamente como se até três instruções (INSERT, UPDATE e/ou DELETE) fossem executadas, dependendo do que é especificado como ações na instrução MERGE.

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 têm o mesmo fuso horário UTC registrado na coluna correspondente ao início do período SYSTEM_TIME.

Quando você executa qualquer consulta de modificação de dados em uma tabela temporal, o Mecanismo de Banco de Dados adiciona uma linha à tabela de histórico, mesmo que nenhum valor de coluna seja alterado.

Como faço para consultar dados temporais?

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

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

Diagrama que mostra como funciona a Consulta Temporal.

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;

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 na coluna ValidTo da tabela que está sendo consultada. Para obter a sintaxe completa e exemplos, consulte Cláusula FROM mais JOIN, APPLY, PIVOT e Consultar dados em uma tabela temporal com controle de versão do sistema.

Expression Linhas qualificadas Observação
AS OF date_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, é realizada uma união entre a tabela temporal e sua tabela de histórico. 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 ValidFrom < end_date_time AND ValidTo > start_date_time Retorna uma tabela com os valores de todas as versões de linha que estavam ativas dentro do intervalo de tempo especificado, independentemente de terem começado antes do valor do parâmetro start_date_time para o argumento FROM ou parado após o valor do parâmetro end_date_time para o argumento TO. Internamente, é realizada uma união entre a tabela temporal e sua tabela de histórico. 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 marco de delimitação inferior definido pelo ponto de extremidade FROM não são incluídas, e os registros que se tornaram ativos exatamente no marco de delimitação superior definido pelo ponto de extremidade TO também não são incluídos.
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time Semelhante à descrição anterior para FOR SYSTEM_TIME FROM start_date_time TO end_date_time, exceto que a tabela de linhas retornadas inclui linhas que se tornaram ativas no marco de delimitação 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 de todas as versões de linha que foram abertas e fechadas dentro do intervalo de tempo especificado definido pelos 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. De forma semelhante, as instruções INSERT e BULK INSERT continuam como se essas novas colunas de período não estivessem presentes (e os valores da coluna são preenchidos automaticamente).

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

Exemplos

Conteúdo relacionado