skip to Main Content

Temporal Table no SQL Server – 1ª Parte

Temporal Table, também chamada de System-Versioned Tables, é uma nova funcionalidade que armazena todo o histórico de alteração dos dados em uma tabela. Esta funcionalidade está presente no padrão ANSI SQL 2011 e foi incluída pela Microsoft no SQL Server 2016.

Uma tabela histórico é gerada para armazenar UPDATEs e DELETEs, sendo possível acessar todo histórico de alteração.

Alguns cenários de uso:

  • Auditoria
  • Recuperação de dados (exclusão ou alteração acidental de dados)
  • Analise de dados comparando com os valores anteriores
  • Carga de dados (ETL) em Data Warehouse

Existem algumas restrições no uso de Temporal Table:

  • É obrigatório ter Primary Key na tabela.
  • Duas colunas DATETIME2 devem ser criadas, pode ser utilizada a opção HIDDEN para esconde-las dos usuários.
  • Não pode ter Trigger INSTEAD OF
  • A tabela não pode ser IN-MEMORY OLTP
  • A tabela não pode ser FILETABLE
  • Não pode utilizar TRUNCATE TABLE

Para lista completa de restrições: https://msdn.microsoft.com/en-us/library/mt604468.aspx

Para criar uma tabela com Temporal Table utilize o script abaixo:

CREATE TABLE dbo.Cliente (
Cliente_ID int not null primary key,
Nome varchar(50) not null,
RendaMensal decimal(10,2) null,
RendaAnual as RendaMensal * 12,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) 
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Cliente_Hist))

INSERT dbo.Cliente (Cliente_ID,Nome,RendaMensal)
VALUES 
(1,'Paulo',10000.00),
(2,'Ana',20000.00),
(3,'Katia',30000.00)

O script acima cria tabela Cliente com tabela histórico de nome Cliente_Hist, para criar com nome padrão basta omitir a cláusula HISTORY_TABLE. A cláusula HIDDEN torna as colunas de controle de alterações (SysStartTime,SysEndTime) invisíveis aos usuários no SELECT *.

SELECT * FROM dbo.Cliente

SELECT Cliente_ID, Nome, RendaMensal, RendaAnual, SysStartTime, SysEndTime 
FROM dbo.Cliente

 

HABILITANDO TEMPORAL TABLE EM TABELA PRÉ-EXISTENTE

Quando a tabela já existe utilizamos o ALTER TABLE para adicionar as duas colunas de controle DATETIME2 e depois outro ALTER TABLE habilitando:

CREATE TABLE dbo.Produto (
Produto_ID int not null primary key,
Descricao varchar(50) not null,
ValorUnitario decimal(10,2) null)

ALTER TABLE dbo.Produto ADD
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN
CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME(),
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN
CONSTRAINT DF_SysEnd DEFAULT CONVERT(datetime2, '9999-12-31 23:59:59'),   
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

ALTER TABLE dbo.Produto
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Produto_Hist))

No próximo post vou falar sobre a opções de consulta da tabela de histórico e como proceder alterações de Schema após habilitar Temporal Table.

Saudações Tricolores,

Landry

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Back To Top