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:
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!
ManageBI 