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.608 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!