Introdução
Quem nunca recebeu a pergunta “qual era o salário desse funcionário em março?” e descobriu que ninguém guardou o histórico? As system-versioned temporal tables resolvem isso de forma nativa: o próprio SQL Server mantém uma history table com todas as versões anteriores de cada linha, sem trigger e sem código na aplicação.
Como funciona
Uma temporal table tem duas colunas DATETIME2 marcadas como GENERATED ALWAYS AS ROW START e ROW END, unidas por PERIOD FOR SYSTEM_TIME. A cada UPDATE ou DELETE, a versão antiga da linha vai para a history tablecom o período em que esteve válida.
- Tabela atual: só a versão vigente; ValidoAte é sempre 9999-12-31 23:59:59.9999999.
- History table: versões passadas. Pode ser criada automaticamente ou apontada para uma tabela existente.
- Horário em UTC: os valores são gravados em UTC e refletem o início da transação, não o momento do comando.
- HIDDEN: as colunas de período podem ficar ocultas de SELECT *, evitando quebrar a aplicação.
Mão na massa
USE DP800_Lab;
GO
-- 1. Criando uma temporal table com history table nomeada
CREATE TABLE dbo.Funcionario
(
FuncionarioId INT NOT NULL PRIMARY KEY CLUSTERED,
Nome NVARCHAR(100) NOT NULL,
Cargo NVARCHAR(50) NOT NULL,
Salario DECIMAL(10,2) NOT NULL,
-- Colunas de período (HIDDEN = não aparecem no SELECT *)
ValidoDe DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
ValidoAte DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidoDe, ValidoAte)
)
WITH
(
SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.FuncionarioHistorico,
HISTORY_RETENTION_PERIOD = 6 MONTHS -- limpeza automática do histórico
)
);
GO
-- 2. Gerando histórico: insert, updates e delete com pausas
INSERT INTO dbo.Funcionario (FuncionarioId, Nome, Cargo, Salario)
VALUES (1, N'Ana', N'DBA Júnior', 5000),
(2, N'Bruno', N'Dev', 6000);
WAITFOR DELAY '00:00:02';
UPDATE dbo.Funcionario SET Cargo = N'DBA Pleno', Salario = 8000 WHERE FuncionarioId = 1;
-- Guardando um "ponto no tempo" para usar no AS OF
DECLARE @Marco DATETIME2 = SYSUTCDATETIME();
SELECT @Marco AS MarcoUTC;
WAITFOR DELAY '00:00:02';
UPDATE dbo.Funcionario SET Cargo = N'DBA Sênior', Salario = 12000 WHERE FuncionarioId = 1;
DELETE FROM dbo.Funcionario WHERE FuncionarioId = 2;
-- 3. Estado atual (repare que o SELECT * não traz as colunas HIDDEN)
SELECT * FROM dbo.Funcionario;
-- 4. Como os dados estavam no momento do @Marco
SELECT FuncionarioId, Nome, Cargo, Salario, ValidoDe, ValidoAte
FROM dbo.Funcionario
FOR SYSTEM_TIME AS OF @Marco;
GO
-- 5. Linha do tempo completa da Ana (atual + histórico)
SELECT FuncionarioId, Nome, Cargo, Salario, ValidoDe, ValidoAte
FROM dbo.Funcionario
FOR SYSTEM_TIME ALL
WHERE FuncionarioId = 1
ORDER BY ValidoDe;
-- 6. Recuperando um registro excluído por engano
INSERT INTO dbo.Funcionario (FuncionarioId, Nome, Cargo, Salario)
SELECT TOP (1) FuncionarioId, Nome, Cargo, Salario
FROM dbo.FuncionarioHistorico
WHERE FuncionarioId = 2
ORDER BY ValidoAte DESC;
As cinco subclauses do FOR SYSTEM_TIME são o coração da matéria:
| Subclause | Retorna linhas que… | Limite superior |
| AS OF @t | estavam ativas no instante @t | — |
| FROM @a TO @b | estiveram ativas em algum momento entre @a e @b | exclusivo |
| BETWEEN @a AND @b | idem ao FROM … TO | inclusivo |
| CONTAINED IN (@a, @b) | começaram e terminaram dentro do intervalo | inclusivo |
| ALL | todas as versões, atual e histórico | — |
Convertendo uma tabela que já existe:
-- Tabela comum, já em produção
CREATE TABLE dbo.Produto
(
ProdutoId INT NOT NULL PRIMARY KEY,
Nome NVARCHAR(100) NOT NULL,
Preco DECIMAL(10,2) NOT NULL
);
GO
-- Adiciona as colunas de período com DEFAULT para as linhas existentes
ALTER TABLE dbo.Produto ADD
ValidoDe DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
CONSTRAINT DF_Produto_ValidoDe DEFAULT SYSUTCDATETIME(),
ValidoAte DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
CONSTRAINT DF_Produto_ValidoAte DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (ValidoDe, ValidoAte);
GO
-- Liga o versionamento
ALTER TABLE dbo.Produto
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProdutoHistorico));
GO
-- Para mudanças de schema mais pesadas: desliga, altera, religa
ALTER TABLE dbo.Produto SET (SYSTEM_VERSIONING = OFF);
-- ... alterações na tabela e na history table ...
ALTER TABLE dbo.Produto
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProdutoHistorico, DATA_CONSISTENCY_CHECK = ON));
GO
Pegadinhas
- TRUNCATE TABLE não é permitido com SYSTEM_VERSIONING = ON.
- Não é possível fazer INSERT ou UPDATE direto na history table enquanto o versionamento está ligado.
- DROP TABLE exige desligar o versionamento antes; a history table fica para trás e precisa ser dropada separadamente.
- Triggers INSTEAD OF não são suportadas na tabela atual nem na history table.
- Duas atualizações da mesma linha dentro da mesma transação geram uma versão com período de duração zero, que não aparece em AS OF.
- A retenção só funciona se TEMPORAL_HISTORY_RETENTION estiver ligado no banco (o padrão é ON).
Conclusão
Temporal tables são das features com melhor custo-benefício do SQL Server: poucas linhas de DDL entregam auditoria, time travel e recuperação de dados. Para a prova, domine as subclauses do FOR SYSTEM_TIME e o fluxo de desligar e religar o versionamento. No próximo post saímos dos limites do banco com as external tables.
Link para os scripts no Github
-- Limpeza opcional desta parte
ALTER TABLE dbo.Funcionario SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Produto SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS dbo.Funcionario, dbo.FuncionarioHistorico,
dbo.Produto, dbo.ProdutoHistorico;