Como utilizar as tabelas temporais do SQL Server? Para controlo de versões

Introdução às Tabelas Temporais do SQL Server para gerir automaticamente a versão dos dados.

As tabelas temporais no SQL Server, também conhecidas como tabelas de versão do sistema, são um recurso introduzido no MS SQL Server 2016. Elas fornecem suporte integrado para armazenar dados históricos relacionados a alterações feitas nos dados de uma tabela. Esta funcionalidade é benéfica para fins de auditoria ou análise de dados de negócios, onde é crucial entender o estado dos dados em qualquer momento. E também para todas as empresas sujeitas a relatórios regulatórios com obrigações legais de transparência.

Sintaxe básica da tabela temporal MS SQL

Vamos começar criando uma tabela temporal básica no Transact-SQL. Assim, o código T-SQL abaixo cria uma tabela chamada Employee com versionamento de sistema. As colunas SysStartTime e SysEndTime são usadas para registrar o período de cada linha. Para fazer um pequeno resumo, são necessários 3 elementos adicionais para criar e gerir uma tabela temporal SQL Server:

• SysStartTime coluna do sistema para marcar o início do período, suporta apenas o tipo de dados DATETIME2.
• SysEndTime para marcar o fim, suporta apenas o tipo de dados DATETIME2.
• Uma tabela de histórico do sistema, aqui é chamada de dbo.EmployeeHistory, com a mesma estrutura, exceto as chaves.

A instrução de criação da tabela é específica

A definição da tabela é especial, no entanto, e esse é o objetivo, inserir dados em uma tabela com versionamento de sistema é semelhante a inserir dados em qualquer outra tabela SQL Server. De fato, as tabelas com versionamento de sistema, ou seja, tabelas temporais são projetadas para gerir automaticamente a versão dos dados, particularmente útil para gerir dimensões e dimensões de mudança lenta, chamadas SCD.

CREATE TABLE [Expert-Only].dbo.Employee
(
   EmployeeId INT PRIMARY KEY,
   EmployeeName NVARCHAR(100),
   Position NVARCHAR(100),
   Salary DECIMAL(18, 2),
   SysStartTime  DATETIME2 GENERATED ALWAYS AS ROW START,
   SysEndTime    DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (
   HISTORY_TABLE = dbo.EmployeeHistory)
);

Inserir dados na tabela temporal

Portanto, esta instrução básica de INSERT T-SQL irá inserir uma nova linha na tabela Employee e atualizará a tabela de histórico associada quando os dados forem alterados.

INSERT INTO [Expert-Only].dbo.Employee (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer', 80000);

Estrutura da tabela de versão do sistema e tabela de histórico

Como você pode ver na imagem abaixo, a tabela Employee agora possui versionamento de sistema e a tabela EmployeeHistory está localizada logo atrás dela no explorador SSMS. Ambas as tabelas têm a mesma estrutura, exceto, é claro, as chaves primárias, porque a tabela de histórico precisa armazenar várias versões de linhas com os mesmos IDs.

Atualizar dados SQL em tabelas temporais

Quando você atualiza um registro em uma tabela com versionamento de sistema, o SQL Server insere automaticamente uma cópia da linha antiga na tabela de histórico. Assim, você pode analisar qualquer alteração passada para entender melhor seus dados. Como resultado, após executar este comando SQL, a tabela EmployeeHistory terá um registro com o salário antigo. Para ilustrar o conceito, a seguinte consulta dispara uma inserção com o valor antigo da linha, diretamente dentro da tabela de histórico.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

Eliminar dados de tabelas com versionamento de sistema

Ao excluir registros em uma tabela com versionamento de sistema, os dados não serão removidos completamente. Os registros excluídos serão movidos para a tabela de histórico. Após executar o comando T-SQL abaixo, a tabela EmployeeHistory terá o registro excluído.

DELETE FROM Employee
   WHERE EmployeeId = 1;

Portanto, é claro que você precisa estar ciente de quão frequentemente os dados mudam e também sobre os volumes de dados para evitar acabar com enormes bases de dados e backups. Você pode ver isso como o mesmo princípio da Windows Lixeira, de fato você pode ter várias versões do mesmo arquivo, excluído mas ainda na lixeira, até esvaziá-la.

Uma solução para isso é implementar um sistema de purga que periodicamente exclua registros antigos da tabela de histórico.

Sintaxe da consulta T-SQL para verificar dados de tabelas temporais

O SQL Server fornece a cláusula FOR SYSTEM_TIME para consultar dados de uma tabela com versionamento de sistema. Este comando SQL retornará os dados como estavam no momento especificado. Você pode usar mais detalhes, é claro, para ser mais específico. Você pode ir até

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10';

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 15:30:45.1234567';

Testar um dado temporal para ver como funciona

Para fazer um teste mais aprofundado, vamos recapitular todas as ações realizadas e adicionar uma, nesta ordem, para ver se a tabela acompanha adequadamente nossas alterações. Entre cada passo, aguarde alguns minutos para melhor visibilidade na tabela de histórico.

1. Inserir a primeira linha com um salário de 80’000 dólares (feito na segunda seção do tutorial)
2. Atualizar o salário para 85’000 dólares
3. Excluir o funcionário da tabela.
4. Inserir novamente o mesmo funcionário, mas desta vez com um salário de 90’000 dólares e um cargo de Developer Senior.
5. Excluir novamente o funcionário da tabela. Não visível na Captura de Ecrã mas disponível no código T-SQL abaixo.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

-- *** Aguarde alguns minutos *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

-- *** Aguarde alguns minutos *** -- 
INSERT INTO [Expert-Only].dbo.Employee
   (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer Senior', 90000);

-- *** Aguarde alguns minutos *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

SELECT * FROM dbo.EmployeeHistory;

-- Primeira inserção
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 10:56:00';

-- Atualização para 85000 $
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:03:00';

-- Segunda inserção como Developer Senior
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:06:00';

Em seguida, verifique o resultado usando o SSMS para selecionar o conteúdo da tabela de histórico e também da tabela Employee em pontos muito específicos no tempo. Como resultado, a tabela Employee está agora vazia. As três linhas arquivadas da tabela EmployeeHistory estão disponíveis usando uma instrução SELECT na própria tabela Employee em 3 pontos diferentes no tempo.

Modificar tabelas existentes com versionamento de sistema

Claro, à medida que os modelos de dados evoluem na base de dados ao longo do tempo, é necessário manter a tabela de histórico quando a tabela versionada muda. Para modificar o esquema de uma tabela com versão de sistema, por exemplo, para adicionar uma nova coluna à tabela Empregado, siga estes 3 passos, nesta ordem:

1. Primeiro, desative a versão do sistema.
2. Faça as alterações na tabela temporal.
3. Depois, ative novamente a versão do sistema.

Eliminar uma tabela com versão de sistema

Como é um tipo específico de tabela, para eliminá-la, também precisa, neste caso, parar manualmente a versão antes de eliminar a tabela. Para eliminar uma tabela com versão de sistema sem erro, use a opção de geração de script do SSMS para gerar a instrução completa. Como pode ver abaixo, realiza 3 operações:

1. Parar a versão.
2. Eliminar a tabela com versão de sistema.
3. Eliminar a tabela temporal que contém o histórico.

Se tentar eliminar a tabela sem parar a versão, o sistema devolve este erro:

Msg 13552, Nível 16, Estado 1, Linha 1 A operação de eliminação da tabela falhou em ‘Expert-Only.dbo.Employee’ porque não é uma operação suportada em tabelas temporais com versão de sistema.

---

Conclusão sobre a configuração de tabelas temporais no SQL Server

Em conclusão, as Tabelas Temporais do SQL Server oferecem uma solução integrada para acompanhar as alterações de dados ao longo do tempo. Elas podem ser valiosas em situações onde é necessário uma auditoria ou análise de dados. Para cenários reais de empresa, quando tinha de fazer todos os passos de arquivo com código personalizado ou SCD, era complexo e pesado de manter. Usar esta funcionalidade de tabela com versão de sistema torna-a muito mais simples de implementar.

Ao compreender como criar, inserir, atualizar, eliminar e consultar dados destas tabelas, pode-se tirar partido desta poderosa funcionalidade no MS SQL Server. Para aprofundar e descobrir mais casos de uso para tabelas temporais, um exemplo típico da vida real é quando se carrega uma tabela de dimensão de um armazém de dados utilizando um pacote SSIS.

Outros tutoriais sobre os mesmos tópicos que podem interessar-lhe é como gerir o tipo de dados JSON utilizando funções nativas do SQL Server.

Como utilizar as funções JSON do SQL Server?

Para obter informações mais detalhadas sobre como trabalhar com Tabelas Temporais no SQL Server, consulte a documentação oficial da Microsoft.