Temporal Table no SQL Server 2016 – 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))  

Veja abaixo como a tabela Cliente criada acima aparece no Object Explorer:

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