Em algumas situações queremos manter nosso histórico de atualização e mudanças em tabelas como uma forma histórica da versão atual e para isso precisamos de um recurso de versionamento de tabelas para o banco de dados.

Até poderíamos utilizar output inserindo em outra tabela, mas poderia ser um pouco contraproducente, então para isso, o SQL Server tem um recurso de versionamento de tabela chamado System-Versioned Temporal Tables, e é sobre este recurso que iremos falar hoje.

TABELA TEMPORAL

O versionamento de tabela do SQL Server permite que seus usuários mantenham registros de qualquer modificação de uma tabela dentro do banco podendo realizar consultas a qualquer momento.

Este tipo de tabela necessita de duas colunas sendo uma com a linha atual e a outra com link para tabela histórica espelhada com o antigo estado da linha armazenada

Uma vantagem que temos utilizando este recurso em comparação caso escolhêssemos o output é que a tabela versionada atualiza automaticamente enquanto que utilizando output isso não ocorre.

As tabelas temporais necessitam de alguns requisitos antes de serem criadas:

  • Primary key
  • Duas colunas com formato datetime2; para início e fim;
  • Coluna início marcada com a cláusula: generated always as row start;
  • Coluna fim marcada com a cláusula: generated always as row end;
  • Ter a opção na tabela: period for system_time (startcol, endcol);
  • Ativar de fato a opção de tabela temporal: system_versioning =  on;
  • E  o link para tabela histórica que o SQL Server irá criar para manter.

Neste primeiro exemplo, quero mostrar como criamos uma tabela versionada:

Como podemos ver, a tabela foi criada com sucesso. Veja a seguir.

Se no seu ambiente houver necessidade de implementar uma tabela temporal e não está querendo criar uma do zero, saiba que é possível alterar uma já existente. veja abaixo 

Se prestarmos atenção na criação das tabelas temporais as duas colunas sysstart e sysend foram marcadas como HIDDEN, isso quer dizer que em um select que não explicitar as colunas, elas não irão aparecer, mesmo utilizando o select *.  veja: 

REALIZANDO OPERAÇÕES NAS TABELAS 

Como mencionado no início, quando realizamos qualquer modificação na tabela que se tornou temporal, o SQL Server automaticamente atualiza a tabela histórica independente do comando utilizado. 

O engine do banco simplesmente move as linhas alteradas para tabela histórica armazenando o registro

Vejamos abaixo o update feito no salário dos funcionários na tabela que foi na tabela que foi versionada a employeeinfo. Perceba também que ela passa a contar a data de modificação na coluna sysstart que foi a coluna que criamos para manter o histórico.

A coluna sysend  não alterou o padrão de data dela pois esta é a tabela versionada, logo, só conta quando iniciamos a modificação. Agora veja a tabela histórica exatamente após o commit do update.

Além de ter sido atualizada automaticamente, como já destacamos aqui, a tabela histórica armazenou na sua coluna sysend o momento que o update na tabela temporal foi finalizado e passado para tabela histórica.

Agora veja o resultado quando executo um segundo update, desta vez aumentando o salário em 2%. Irei mostrar apenas a tabela histórica EmployeeInfoHistory.

O que aconteceu nesta tabela é que no primeiro update ela não gerou um horário de início pois não tinha histórico para esta tabela. Porém, quando executamos o segundo update dando um novo aumento, aquele primeiro horário da coluna sysend  passa a informar na coluna sysstart quando o novo valor da coluna rate passou a valer.

Já na coluna sysend o que temos marcado ali é o horário que o novo update com o aumento de 2% foi finalizado pelo engine do banco.

NOTA: o SQL Server sempre irá considerar  o horário que a operação se inicia. Então, se um update começar 12:00 e terminar às 13:00, o horário marcado  na tabela será 12:00.

SELECIONANDO DADOS NAS TABELAS TEMPORAIS

selecionar dados nas tabelas temporais que criamos não difere de uma tabela normal assumindo que desejamos visualizar o estado atual dos dados ali armazenados. Mas e se quisermos visualizar os dados antes das modificações realizadas? Como poderíamos fazer isso?

Quando queremos realizar este tipo de operação precisamos declarar na consulta uma cláusula chamada for system_time.

Primeiro, recriei a tabela e inseri novos dados para um novo teste. Após esta operação, executei um select simples onde podemos observar e comprovar conforme a coluna sysstart que os dados foram inseridos às 17:08. Query destacada em preto.

Depois deste select, executamos um novo select desta vez com a cláusula for system_time para verificar o estado anterior desta tabela. Como ela não tinha nenhum valor armazenado, nos retornou vazio. Query destacada em vermelho. 

No próximo passo, vamos realizar um update na tabela temporal e executar um novo select para verificar os dados no estado anterior ao update. E após, um novo select na tabela histórica e um outro select com a cláusula for system_time.  

A query circulada em vermelho é o registro pré-update dos salários.

Como podemos ver, agora que temos um histórico de modificação e atualização na tabela temporal, conseguimos visualizar as modificações executadas sem precisar selecionar a tabela histórica.

Um outro recurso interessante é realizar operações de comparação entre as duas tabelas facilitando uma análise histórica. Veja o exemplo.

SUB CLÁUSULAS DA CLÁUSULA FOR SYSTEM_TIME

A cláusula for system_time possui algumas sub cláusulas que permitem maior refino na busca dos dados armazenados na coluna. Cada cláusula age de uma forma diferente e claro, retornam resultados diferentes.

A primeira subcláusula que iremos ver é a from – to. ela funciona logicamente da seguinte forma: sysstart < sysend and sysend > sysstart.

Imagine que não sabemos exatamente o horário de uma alteração X, mas que ela ocorreu horas antes desta query, logo, para podermos visualizar quais mudanças aconteceram, temos que executar a query usando a subcláusula from no for system_time

Na query acima por utilizarmos o from como subcláusula o processamento lógico para retorno de resultado foi:

  • Ela retornou os resultados da coluna sysstart que iniciaram antes do horário que foi utilizado como final na subcláusula from;
  • A coluna sysend retornou os resultados que ocorreram após o horário inicial na subcláusula from;

Veja a query abaixo e perceba o seguinte: O tempo na minha coluna sysstart é sempre menor que o tempo que foi declarado após a cláusula TO;

E o tempo na coluna sysend é sempre maior que o tempo declarado após a cláusula FROM. Formando uma espécie de cruzamento.

Se após executar a query e ainda assim, não identificar a alteração na tabela, tente mudar a lógica de execução com os operadores lógicos between e and. Quando utilizamos estes, a consideração passa a ser valores que são menores ou iguais aos valores de input na query.

O between – and considera que o seguinte:

  • Retorna os valores da coluna sysstart que são menores ou iguais ao horário de input após a cláusula and;
  • Retorna os valores da coluna sysend que são menores ou iguais ao horário de input após a cláusula between.

Já para a próxima subcláusula, irei realizar um novo update aumentando novamente o salário dos funcionários.

Esta subcláusula se chama contained – in e ela é a mais correspondente das três uma vez que faz comparação direta entre os dados. Esta subcláusula quando executada considera o seguinte:

  • Para a coluna sysstart retorna as alterações que são maiores ou iguais ao primeiro horário passado na cláusula contained-in;
  • Para a coluna sysend retorna as alterações que são menores ou iguais ao último horário passado na cláusula contained-in

Repare que utilizando o mesmo horário dos dois últimos exemplos, o retorno dos resultados foram bem menores, uma vez que a maioria ‘não atende’ aos requisitos lógicos da subcláusula contained – in

E se executarmos uma nova query com a mesma subcláusula alterando os horários, inclusive para mostrar o nosso último update? Veja o resultado abaixo: 

Já quando utilizamos a subcláusula ALL temos o retorno de todas as modificações em todos os horários e das duas tabelas, tanto a temporal quanto a histórica. Veja o exemplo:

Esta imagem possuí um atributo alt vazio; O nome do arquivo é image.png

Este exemplo, além dos inserts houve um update com aumento na coluna salário para todos os  funcionários, o que acabou gerando esse resultado maior.

Como podemos perceber, temos aqui todas as operações executadas na tabela, inclusive meu último update sublinhado em vermelho.

Este talvez tenha sido o post mais complexo e completo até o momento, trabalhar com tabelas temporais pode ser um pouco complicado no início, mas quando se pega o conhecimento fica mais simples.

Este recurso se bem aplicado pode ser muito útil para controle de registro e versão de tabelas do banco facilitando em muito restore, operações de merge ou ajustes de updates!

Busquem implementar em tabelas importantes do seu banco e verão a quantidade de benefícios que este recurso pode trazer.

Deixarei o link para caso queiram, baixar o script usado neste post!

Espero que gostem, saúde!