Entendendo Temporal Tables no MS SQL Server 2016

E vamos retomar com as novidades do MS SQL Server 2016 agora Entendendo Temporal Tables.

Estamos fazendo um estudo sobre as novas funcionalidades da ferramentas, passando por mais uma semana bacana de aprendizados e desafios por aqui.

Neste artigo vamos falar sobre Entendendo Temporal Tables, – cuidado para não confundir com as tabelas temporárias ou vulgo TempDB – abordando uma visão prática do que é possível ser feito com este novo recurso do Microsoft SQL Server 2016.

Primeiramente vou abordar uma parte mais conceitual e teórica e em um próximo artigo, vamos mergulhar mais a fundo em exemplos práticos.

Entendendo Temporal Tables em um cenário real

Realmente esta versão do Microsoft SQL Server 2016 é fantástica! Em 2011, participei de um treinamento para preparação das provas de certificação do MCDBA e MCITP na Ka Solution, em São Paulo.

Na época, a versão mais popular e utilizada era o MS SQL Server 2008 e lembro muito bem do trabalho que era propor com o SQL esta solução de histórico, separar arquivos mdf de ndf e enfim, este universo de coisas que tomavam um certo tempo.

Sem contar nas vezes que você encontrava algumas malditas triggers escritas para fazer estas operações ou alimentar o histórico, terrível!

O cenário perfeito para o uso das Temporal Tables é o seguinte: imagine que você tenha uma entidade ou tabela chamada Pedidos de seu sistema, e você precise auditar os dados dos Produtos que foram de alguma forma excluídos.

Ai complicou, não é? Calma! Ai que entra a Temporal Tables.

Estes são os casos mais comuns para utilização:

Auditar dados -Você pode descobrir quais valores uma entidade específica teve durante todo o seu ciclo de vida útil.

Dimensões Slowly Changing – Em sistemas de processamento de transações, não é raro ter que analisar como métricas importantes sofrem alterações ao longo do tempo. Idealmente, analisar o histórico não deve comprometer o desempenho do aplicativo OLTP em que o acesso ao estado mais recente dos dados deve ocorrer com latência mínima e o mínimo de bloqueio de dados.

Recuperar registros em nível de gravação (record-level) – Pense nisso como uma espécie de mecanismo de backup em uma única tabela. Você removeu acidentalmente um registro? Recupere-o da tabela de histórico e insira-o novamente na tabela principal.

As tabelas temporais atualmente não suportam o controle de versão, o que significa a versão de registros com base em datas lógicas.

Por exemplo, suponha que você tenha uma tabela contendo os preços dos produtos. Se você atualizar o preço às 12hs usando uma instrução UPDATE, a tabela temporal manterá o histórico do preço antigo até às 12hs desse dia. A partir das 12hs, o novo preço é válido.

No entanto, e se a mudança de preço fosse realmente prevista para começar a partir de 13hs (uma mudança lógica). Isso significa que você precisa ajustar sua indicação de atualização perfeitamente para que ela funcione e você deve ter executado a instrução UPDATE para 13hs em vez de 12hs.

Criando uma tabela com versão de sistema

Vamos a parte prática de Entendendo Temporal Tables. Quando você quer criar uma nova tabela temporal, alguns pré-requisitos devem ser atendidos:

• Uma chave primária deve ser definida
• Duas colunas devem ser definidas para registrar a data de início e término com um tipo de dados de DateTime2. Se necessário, essas colunas podem ser escondidas usando o sinalizador HIDDEN. Essas colunas são chamadas de colunas de período SYSTEM_TIME.
• As instruções de trigger INSTEAD OF não são permitidos.
• O recurso In-Memory OLTP não pode ser usado

Há também algumas limitações:

• Tabela temporal e histórica não pode ser FILETABLE;
• A tabela de histórico não pode ter restrições;
• As instruções INSERT e UPDATE não podem fazer referência às colunas do período SYSTEM_TIME;
• Os dados na tabela de histórico não podem ser modificados

Para uma lista completa de considerações e limitações, consulte a seção correspondente das tabelas temporais da página MSDN.

O script a seguir cria uma tabela simples com versões de sistema:

Se você não especificar um nome para a tabela de histórico, o SQL Server irá gerar  automaticamente uma das seguintes estruturas:

dbo.MSSQL_TemporalHistoryFor_xxx , onde xxx é o id do objeto da tabela principal.

A tabela de histórico tem um conjunto de colunas idêntico a entidade original, mas com todas as constraints removidas. Ele também tem seu próprio conjunto de índices e estatísticas.

A criação de seus próprios índices, como um índice cluster do tipo columnstore na tabela de histórico, pode melhorar consideravelmente o desempenho.

Observe que também é possível ativar o versionamento do sistema em uma tabela existente. Ou você já possui uma tabela de histórico existente e você apenas a inclui na declaração ALTER TABLE, ou você cria uma você mesmo.

Observe que a coluna SysEndTime não é necessária, uma vez que ele apenas exibe o valor máximo datetime2.

O histórico exibe as versões antigas das diferentes linhas e elas são devidamente datadas.