SQL Server 2016 - How to "time travel" using the Temporal Tables feature

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.

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’.

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

Result:

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:

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.

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:
  

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:

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)