Tabelas temporais

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Base de Dados SQL do Azure Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

As tabelas temporais (também conhecidas como tabelas temporais com controlo de versões do sistema) são uma funcionalidade de uma base de dados que disponibiliza suporte incorporado para informações sobre os dados armazenados na tabela em qualquer momento no tempo, e não apenas sobre os dados atuais.

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 gerida pelo sistema é um tipo de tabela de utilizador concebida para manter um histórico completo das alterações aos dados, permitindo a análise num determinado momento no tempo. Este tipo de tabela temporal é chamado de tabela temporal com versões geridas pelo sistema, porque o sistema gere o período de validade de cada linha (isto é, o Database Engine).

Cada tabela temporal tem duas colunas explicitamente definidas, cada uma com um datetime2 tipo de dados. Estas colunas são chamadas colunas de época . O Database Engine utiliza exclusivamente estas colunas de período para registar o período de validade de cada linha sempre que uma linha é modificada. A tabela principal que armazena os dados atuais chama-se tabela atual, ou 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 reais de dados são dinâmicas, e as decisões empresariais dependem frequentemente de insights que os analistas obtêm 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
  • Reconstrução do estado dos dados em qualquer momento do 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 adicionais datetime2 definem 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 ValidFrom coluna 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 da ValidTo coluna 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 ValidTo coluna 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.

  • Eliminações: O sistema armazena o valor anterior da linha na tabela de histórico e define o valor da ValidTo coluna 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.

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

As horas registadas nas colunas datetime2 do sistema são baseadas 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 usa a FOR SYSTEM_TIME cláusula com uma das cinco subcláusulas numa consulta, os resultados incluem dados históricos da tabela temporal, como mostrado na imagem seguinte.

Diagrama que mostra 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).

O Database Engine gera essas linhas se realizares múltiplas 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.

Expression Linhas de qualificação Note
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 data_hora_inicioTOdata_hora_fim 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_inicioANDdata_hora_fim ValidFrom <= end_date_timeAND ValidTo >start_date_time 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 >= data_hora_inicioAND ValidTo <=data_hora_fim 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

Pode ocultar as colunas de período, para que as consultas SELECT que não lhes façam referência explícita não devolvam essas colunas, como 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 detalhes sobre o uso da HIDDEN cláusula, veja CREATE TABLE e ALTER TABLE.

Samples

  • 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

  • Last updated on