Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables

Visualizações: 2.871 views
Esse post é a parte 9 de 21 da série Segurança e Auditoria
Tempo de Leitura: 7 minutos

Fala galera!!

Através deste post, gostaria de mostrar a vocês um recurso muito interessante e útil, que são as Temporal Tables. Conforme já havia comentado sobre eles no post SQL Server 2016 – Lista de novidades e novos recursos, onde é possível recuperar os dados de uma tabela em qualquer ponto de tempo. Isso quer dizer que você pode fazer uma “viagem no tempo”, visualizando quais eram os dados de uma determinada tabela em uma data e hora específicas, utilizando simples consultas SQL.

Esse recurso tem uma vasta gama de possibilidades e utilidades, das quais posso destacar o Slowly changing dimensions do BI, para implementar uma dimensão cuja visão é a de uma determinada data, e também para fins de Auditoria, comparando o passado com o atual. Tudo isso sem precisar criar triggers e nenhum controle manual.

Vale ressaltar que esse recurso está disponível em todas as edições do SQL Server, inclusive a Web Edition e Express.

Como funcionam as Tabelas Temporais?

Visualizar conteúdo
Uma tabela temporal nada mais é que uma tabela comum, que possui um versionamento de sistema para gravar os dados alterados em uma tabela de histórico (definida por você) com duas colunas do tipo DATETIME2 para controlar a validade do registro e assim, permitir que esse recurso funcione. A tabela original vai manter sempre os dados atuais e a tabela de histórico irá gravar os registros antigos, conforme demonstrado na imagem abaixo:

Ao consultar os dados especificando a data desejada, os registros das 2 tabelas serão considerados, de forma transparente para você, conforme vou demonstrar logo a seguir no próximo tópico.

Restrições de uma Tabela Temporal

Visualizar conteúdo
Uma parte importante no processo de implementação de um novo recurso, é entender como o mesmo funciona e suas restrições, que vou listar abaixo:

  • A tabela em questão deve ter uma PRIMARY KEY definida para poder utilizar o recurso de versionamento. Caso contrário, você verá uma mensagem como essa:
    Msg 13553, Level 16, State 1, Line 4
    System versioned temporal table ‘dirceuresende.dbo.Tabela_Temporal’ must have primary key defined.
  • Uma vez que você crie uma tabela com esse recurso ativado, você não poderá realizar a operação de TRUNCATE TABLE nesta tabela, resultando na seguinte mensagem de erro:
    Msg 13545, Level 16, State 1, Line 58
    Truncate failed on table ‘dirceuresende.dbo.Tabela_Temporal’ because it is not supported operation on system-versioned tables.
  • Você não conseguirá mais excluir a tabela que está com um versionamento ativo. Para fazer isso, precisará parar o versionamento e depois excluir a tabela. Caso contrário, verá a seguinte mensagem de erro:
    Msg 13552, Level 16, State 1, Line 58
    Drop table operation failed on table ‘dirceuresende.dbo.Tabela_Temporal’ because it is not supported operation on system-versioned temporal tables.
  • In-memory OLTP não pode ser utilizado
  • Triggers INSTEAD OF não são permitidas. Triggers AFTER só permitidas apenas na tabela atual (Não pode criar na tabela histórica).
  • A tabela histórica não pode possuir constraints
    Msg 13564, Level 16, State 1, Line 1
    Adding CHECK constraint to a temporal history table ‘dirceuresende.dbo.Tabela_Temporal_Historico’ is not allowed.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint or index. See previous errors.
  • Os dados na tabela histórica não podem ser modificados manualmente (UPDATE, DELETE, INSERT, etc)
    Msg 13560, Level 16, State 1, Line 1
    Cannot delete rows from a temporal history table ‘dirceuresende.dbo.Tabela_Temporal_Historico’.
  • As instruções INSERT e UPDATE não podem fazer referência às colunas do período SYSTEM_TIME
    Msg 13537, Level 16, State 1, Line 5
    Cannot update GENERATED ALWAYS columns in table ‘dirceuresende.dbo.Tabela_Temporal’.

Quais tabelas são temporais na minha base?

Visualizar conteúdo
Para verificar quais tabelas estão com o recurso do versionamento de sistema ativado, basta executar essa query abaixo:

Resultado:

Como criar uma Tabela Temporal (Versionada)?

Visualizar conteúdo
Para criar uma tabela com o versionamento de sistema ativado, você precisará criar 2 colunas na sua tabela para determinar a validade dos registros:

  • Dt_Inicio DATETIME2 GENERATED ALWAYS AS ROW START
  • Dt_Fim DATETIME2 GENERATED ALWAYS AS ROW END

E também será adicionada a cláusula PERIOD FOR SYSTEM_TIME (Dt_Inicio, Dt_Fim) após essas colunas e também a cláusula WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Tabela_Historico)) ao final do comando de CREATE da sua tabela.

Agora vou demonstrar um CREATE TABLE completo para facilitar a entender como seria a criação de uma tabela temporal:

Após criar uma tabela temporal (versionamento de sistema), a tela do Object Explorer do SQL Server Management Studio irá mostrar a tabela da seguinte maneira:

Como converter uma tabela comum para Tabela Temporal?

Visualizar conteúdo
Para converter uma tabela comum para Tabela Temporal, basta executar alguns comandos de ALTER TABLE para criar as colunas de definição de período (Dt_Inicio e Dt_Fim) e ativar o versionamento na tabela, conforme exemplo abaixo:

E se a tabela já possuir registros ? Bom, neste caso, basta criar as colunas com uma constraint para incluir um valor DEFAULT nas colunas que serão criadas agora e que não possuem valor:

Com a query acima, você irá criar as novas colunas com Dt_Inicio = data/hora atual e Dt_Fim = data máxima do datetime2, ou seja, todos os registros da tabela serão considerados os registros atuais.

Como consultar os dados da Tabela Temporal?

Visualizar conteúdo
Agora que criamos a nossa tabela com o versionamento de sistema ativado, transformando nossa tabela em uma tabela temporal ou temporal table, vamos gerar algumas informações e depois visualizar como podemos consultá-las:

Resultado da nossa tabela:

Nos exemplos acima, utilizei a cláusula AS OF ‘data’. Existem 5 tipos de cláusulas para especificar o período de datas desejado:

  • ALL: Retorna todas as alterações realizadas na tabela

    Exemplo:

  • AS OF: Recurso point-in-time, ou seja, retorna os dados em uma data e hora específica

  • FROM <start_date_time> TO <end_date_time>: Retorna todos os registros cujo campo Dt_Inicial < data final informada e campo Dt_Final > data inicial informada.

    Exemplo:

  • BETWEEN<start_date_time> AND <end_date_time>: Retorna todos os registros cujo campo Dt_Inicial <= data final informada e campo Dt_Final > data inicial informada. Essa cláusula é muito parecida com a FROM… TO.. com a única diferença que é que a primeira condição considera também os registros com igualdade da condição (<=), enquanto a cláusula FROM.. TO.. não (apenas <)

    Exemplo:

  • CONTAINED IN (<start_date_time> , <end_date_time>): Retorna todos os registros cujo Dt_Inicial >= data inicial informada e campo Dt_Final <= data final informada.

    Exemplo:

Como desativar o versionamento de uma Tabela Temporal?

Visualizar conteúdo
Caso você não queira mais utilizar o recurso de versionamento de sistema, transformando uma tabela temporal numa tabela comum e excluindo todos os metadados e estrutura da tabela temporal, basta você utilizar os comandos abaixo:

Se você quer apenas desativar temporariamente o recurso de versionamento, mas não quer excluir as informações, você pode utilizar o comando abaixo:

A minha tabela de histórico vai crescer para sempre?

Visualizar conteúdo
Um cuidado que você deve ter ao ativar esse recurso na sua instância é relacionado ao espaço em disco consumido pela tabela de histórico. Por padrão, a tabela irá crescer indefinidamente até se esgotar o espaço em disco. Para controlar isso, você pode criar um job que irá fazer o processo de limpeza dos dados, mantendo apenas os dados mais recentes, conforme exemplos abaixo:

Azure SQL Database

SQL Server (On-Premise)

É isso aí, pessoal!
Um grande abraço e até mais!