Partilhar via

Tabelas temporais

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

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

Comece com tabelas temporais versionadas pelo sistemae revise os cenários de uso de tabelas temporais.

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 projetada para manter um histórico completo de alterações de dados, permitindo uma análise fácil em um ponto no tempo. Esse tipo de tabela temporal é conhecido como uma tabela temporal com versão do sistema, porque o sistema gerencia o período de validade de cada linha (ou seja, o Mecanismo de Banco de Dados).

Cada tabela temporal tem duas colunas explicitamente definidas, cada uma com um datetime2 tipo de dados. Estas colunas são chamadas colunas do período . Estas colunas de período são utilizadas exclusivamente pelo sistema para registar o período de validade de cada linha, sempre que uma linha é modificada. A tabela principal que armazena dados atuais é referida 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 de . O sistema usa a tabela de histórico para armazenar automaticamente a versão anterior da linha cada vez 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 (que deve ser compatível com o esquema) ou permitir que o sistema crie uma tabela de histórico padrão.

Porquê temporal?

As fontes de dados reais são dinâmicas e, na maioria das vezes, as decisões de negócios dependem de insights que os analistas podem obter da evolução dos dados. Os casos de uso para tabelas temporais incluem:

  • Auditar todas as alterações de dados e realizar perícia forense de dados quando necessário
  • Reconstruindo o estado dos dados a partir de qualquer momento no passado
  • Calcular tendências ao longo do tempo
  • Manter uma dimensão em mudança lenta para aplicações de apoio à decisão
  • Recuperação de alterações acidentais de dados e erros de aplicativos

Como funciona o tempo?

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 destas tabelas, duas colunas extras de datetime2 são utilizadas para definir o período de validade de cada linha.

  • Coluna de início do período: O sistema regista a hora de início da linha nesta coluna, normalmente designada por coluna ValidFrom.

  • Coluna de fim de período: O sistema registra a hora de término da linha nesta 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 a hora de término para o período para o qual era válida.

Diagrama mostrando como funciona uma tabela temporal.

O script a seguir ilustra um cenário com informações do funcionário:

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 versionada por sistema.

  • Inserções: O sistema define o valor da coluna ValidFrom para 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 ao 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 da coluna ValidTo para 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 da 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 da linha atualizada na tabela atual para a coluna ValidTo permanece o valor máximo de 9999-12-31.

  • Exclui: O sistema armazena o valor anterior da linha na tabela de histórico e define o valor da coluna ValidTo para 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 essa linha. Somente consultas que lidam com dados do histórico retornam dados para os quais uma linha está fechada.

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

As horas registadas no sistema colunas de datetime2 baseiam-se na hora de início da própria transação. Por exemplo, todas as linhas inseridas numa única transação têm a mesma hora UTC registada na coluna correspondente ao início do período de SYSTEM_TIME.

Quando você executa consultas 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 consultar dados temporais?

A instrução SELECT ... FROM <table> tem uma nova cláusula FOR SYSTEM_TIME, com cinco subcláusulas específicas do tempo para consultar dados nas tabelas atual e de histórico. Esta nova sintaxe de instrução SELECT é suportada diretamente numa única tabela, propagada por meio de várias junções e através de visualizações sobre múltiplas tabelas temporais.

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

Diagrama mostrando como funciona a Consulta Temporal.

A consulta a seguir procura versões de linha para um funcionário com a condição de filtro WHERE EmployeeID = 1000 que estiveram ativas pelo menos por uma 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 elimina as linhas que têm um período de validade de duração zero (ValidFrom = ValidTo).

Essas linhas são geradas se você executar várias atualizações na mesma chave primária dentro da mesma transação. Nesse caso, a consulta temporal retorna apenas versões de linha antes das transações e linhas atuais após as transações.

Se você precisar incluir essas linhas na análise, consulte a tabela de histórico diretamente.

Na tabela a seguir, ValidFrom na coluna Linhas qualificadas representa o valor na coluna ValidFrom na tabela que está sendo consultada e ValidTo representa o valor na coluna ValidTo na tabela que está sendo consultada. Para obter a sintaxe completa e exemplos, consulte cláusula FROM mais JOIN, APPLY, PIVOTe dados de consulta em uma tabela temporal com versão do sistema.

Expressão Linhas de qualificação Observação
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Retorna uma tabela com linhas contendo os valores que eram atuais no momento especificado no passado. Internamente, uma união é realizada entre a tabela temporal e sua tabela histórica. Os resultados são filtrados para devolver os valores na linha que eram válidos no momento, indicado pelo parâmetro de data e hora . O valor de uma linha é considerado válido se o valor system_start_time_column_name for menor ou igual ao valor do parâmetro date_time e o valor system_end_time_column_name for maior que o valor do parâmetro date_time.
FROM start_date_timeTOend_date_time ValidFrom < end_date_timeAND ValidTo >start_date_time Retorna uma tabela com os valores para todas as versões de linha que estavam ativas dentro do intervalo de tempo especificado, independentemente de terem começado a estar ativas antes do valor do parâmetro start_date_time para o argumento FROM ou terem deixado de estar ativas após o valor do parâmetro end_date_time para o argumento TO. Internamente, uma união é realizada entre a tabela temporal e sua tabela histórica. Os resultados são filtrados para retornar os valores de todas as versões de linha que estavam ativas a qualquer momento durante o intervalo de tempo especificado. As linhas que deixaram de estar 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 data_hora_inícioANDdata_hora_fim ValidFrom <= data_hora_fimAND ValidTo >data_hora_início Igual ao anterior na descrição do FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time, exceto pela inclusão na tabela de linhas retornadas de linhas que passaram a estar ativas no limite superior definido pelo ponto de extremidade end_date_time.
CONTAINED IN (data_hora_início, data_hora_fim) ValidFrom >= start_date_timeAND 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 pelos dois valores de período para o argumento CONTAINED IN. As linhas que se tornaram ativas exatamente no limite inferior ou deixaram de estar ativas exatamente no limite superior estão incluídas.
ALL Todas as linhas Retorna a união de linhas que pertencem à tabela atual e à tabela 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 retornem essas colunas (por exemplo, ao executar SELECT * FROM <table>).

Para retornar uma coluna oculta, você deve se referir explicitamente à coluna oculta na consulta. Da mesma forma, as instruções INSERT e BULK INSERT continuam como se essas novas colunas de período não estivessem presentes (e os valores das colunas são preenchidos automaticamente).

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

Amostras

  • pt-PT: ASP.NET: Consulte o aplicativo web ASP.NET Core para saber como criar um aplicativo temporal usando tabelas temporais.

  • base de dados de exemplo AdventureWorks: Descarregue a base de dados AdventureWorks para SQL Server, que inclui funcionalidades de tabelas temporais.

Conteúdo relacionado