Tabelas Temporais – Parte 1: Criação de tabelas

No SQL Server 2016 encontramos uma feature baseada na ISO/ANSI SQL:2011 chamada System-Versioned Temporal Tables [1]. Essa “nova” propriedade permite criar ou alterar uma tabela para que suas alterações ao longo do tempo sejam registradas numa tabela de histórico. Isso possibilita que sejam realizadas consultas num determinado momento, como se viajássemos no tempo e executássemos a consulta naquele instante. Isso entre muitas outras capacidades legais que abordarei nesta série de posts. Digo “nova” pois seus concorrentes diretos já possuem esta funcionalidade há um certo tempo… O DB2 v10 já possui este recurso desde 2010, enquanto o Oracle 12c desde 2013 (ou desde 2005 se considerar as Flashback Queries). Sem deixar de mencionar a comunidade do PostgreSQL que vem discutindo isso desde 2012… Sem problemas. Chegamos lá.

Nesta primeira parte irei abordar o básico sobre a criação de uma tabela temporal e algumas considerações acerca do assunto.

Vale ressaltar que, no momento em que este post foi escrito, a versão disponível do SQL Server é a 2016 CTP2.2. Logo, alguns detalhes podem mudar com as novas versões lançadas. Farei um esforço para manter este material atualizado de acordo com todas atualizações, mas isso vai depender da minha disponibilidade.

CRIANDO TABELAS TEMPORAIS

Para criar uma tabela temporal, existem alguns requisitos que devem ser atendidos.

  • A tabela principal deve possuir uma chave primária;
  • A tabela principal deve possuir duas colunas do tipo DATETIME2 (não importa a precisão) que sejam NOT NULL para controlar o início e o fim da validade do registro;
  • A coluna que determina o início da validade do registro deve conter a opção GENERATED ALWAYS AS ROW START;
  • A coluna que determina o fim da validade do registro deve conter a opção GENERATED ALWAYS AS ROW END;
  • Um período de validade do registro deve ser definido com a opção PERIOD FOR SYSTEM_TIME utilizando as duas colunas DATETIME2 criadas;
  • Uma tabela de histórico deve ser associada à tabela principal;
  • A opção de tabela SYSTEM_VERSIONING deve ser definida como ON;

As demonstrações dessa série de posts serão executadas no banco TemporalTestDB. Primeiro vamos criar o banco e apagar as tabelas caso elas existam.

USE [master];
GO
IF DB_ID(N'TemporalTestDB') IS NULL
CREATE DATABASE [TemporalTestDB]
GO
USE [TemporalTestDB];
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.Disciplina',N'U') IS NOT NULL
BEGIN
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Disciplina',N'U'),N'TableTemporalType') = 2 -- É Temporal
ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = OFF);
DROP TABLE [dbo].[Disciplina];
END
IF OBJECT_ID(N'dbo.DisciplinaHistorico',N'U') IS NOT NULL
DROP TABLE [dbo].[DisciplinaHistorico];

A tabela criada inicialmente será a tabela temporal Disciplina que guardará todas alterações na tabela de histórico DisciplinaHistorico. Se preferir consultar a documentação oficial, veja o artigo do BOL CREATE TABLE (Transact-SQL) [2]. Nota: Esta tabela de histórico será criada pelo SQL Server neste exemplo.

CREATE TABLE [dbo].[Disciplina] (
	[id_disciplina] INT CONSTRAINT pk_Disciplina PRIMARY KEY,
	[nome_disciplina] VARCHAR(50) NOT NULL,
	[creditos] INT NOT NULL,
	[valor_credito] MONEY NOT NULL,
	[inicio_registro] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
	[fim_registro] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
	PERIOD FOR SYSTEM_TIME ([inicio_registro],[fim_registro])
)WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DisciplinaHistorico]));

Algumas considerações que não ficam explícitas com esse comando são:

  • A tabela principal pode ser uma HEAP;
  • As colunas que determinam a validade de um registro são gerenciadas pelo SQL Server, ou seja, não é permitido atribuir valores explícitos a estes campos;
  • A tabela de histórico não precisa existir previamente;
  • A tabela de histórico não precisa estar nos mesmos schema ou filegroup que a tabela principal, é possível movê-la para outro filegroup depois;
  • Caso a tabela de histórico seja criada pelo SQL Server, ela possuirá um índice cluster baseado na chave primária e nas colunas que definem a validade do registro na tabela principal;

Vale dizer que a tabela de histórico não precisa ter índices. No entanto, suas consultas temporais sofrerão muito com isso, já que sempre será realizado um table scan na tabela de histórico. A tabela de histórico também pode existir antes da criação da tabela principal. Geralmente ela estará vazia no momento da criação do link entre a tabela principal e ela, mas isso não é obrigatório. Veremos algumas situações inusitadas em um post futuro quando eu for falar sobre DATA_CONSISTENCY_CHECK.

Um outro detalhe interessante de se mencionar é a nova opção HIDDEN para as colunas de validade dos registros. Esta opção não obrigatória faz com que essas colunas não sejam apresentadas quando se executa um SELECT *. Desta forma, as colunas só aparecerão se forem listadas explicitamente no SELECT. Uma ótima notícia para quem deseja baixo impacto na implementação desta nova tecnologia.

CONVERTENDO UMA TABELA EXISTENTE EM TABELA TEMPORAL

Agora vamos supor que você tenha migrado para o SQL Server 2016 e queria utilizar essa tecnologia numa tabela já existente. Quais os passos necessários?

A primeira pergunta é: você já tem uma tabela de histórico criada e mantida por triggers, CDC, etc? Ou você começará a registrar os dados históricos a partir de agora? Vou mostrar a forma mais comum – iniciar a captura de dados históricos do zero – neste post e futuramente explorarei o ajuste de tabelas de histórico já existentes para a ativação do SYSTEM_VERSIONING.

Imaginemos que a tabela Disciplina já existia no banco e continha alguns registros.

CREATE TABLE [dbo].[Disciplina] (
	[id_disciplina] INT CONSTRAINT pk_Disciplina PRIMARY KEY,
	[nome_disciplina] VARCHAR(50) NOT NULL,
	[creditos] INT NOT NULL,
	[valor_credito] MONEY NOT NULL
);

INSERT INTO [dbo].[Disciplina]([id_disciplina],[nome_disciplina],[creditos],[valor_credito]) 
VALUES 
		(1,'Cálculo I',4,100.00),
		(2,'Cálculo II',4,125.00),
		(3,'Cálculo III',4,150.00),
		(4,'Física Teórica I',4,100.00),
		(5,'Física Experimental I',4,75.00),
		(6,'Português',6,100.00);
GO

Para que seja possível ativar o SYSTEM_VERSIONING nesta tabela, é necessário verificar se ela possui chave primária. Como a criamos agora, sabemos que ela já tem. No entanto, poderíamos verificar isso rapidamente com o seguinte trecho de código:

SELECT OBJECTPROPERTY(OBJECT_ID(N'TemporalTestDB.dbo.Disciplina'),'TableHasPrimaryKey')

Resultado 1 significa que sim e 0 que não possui chave primária. Se o resultado for NULL, significa que o objeto não foi encontrado. Talvez por algum erro de digitação. Agora que temos certeza de que ela possui PK, temos de adicionar as duas colunas do tipo DATETIME2 que sejam NOT NULL para determinar a validade do registro. Pelo fato da tabela Disciplina ter registros e ser pequena (e eu ter preguiça) podemos adicionar as duas colunas com default constraints para que possam ter a definição NOT NULL de cara. Aproveitando o mesmo comando, podemos definir a opção PERIOD FOR SYSTEM_TIME de uma vez. Farei isso num comando só, mas é claro que podemos dividi-los em três comandos separados.

ALTER TABLE [dbo].[Disciplina] ADD
	[inicio_registro] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL 
		CONSTRAINT dflt_inicio_registro DEFAULT SYSDATETIME(),
	[fim_registro] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
		CONSTRAINT dflt_fim_registro DEFAULT '9999-12-31 23:59:59',
	PERIOD FOR SYSTEM_TIME ([inicio_registro],[fim_registro]);

Sim, você pode remover essas constraints depois. Quem irá cuidar do preenchimento dessas colunas após a opção SYSTEM_VERSIONING ser habilitada é o SQL Server. Uma coisa importante de se lembrar é que os valores da coluna que define o início da validade do registro devem ser iguais ou menores que o instante de tempo em que você habilitar o versionamento. Caso contrário você pode esbarrar com um erro como o abaixo (clique para aumentar) ao tentar realizar uma operação DML na tabela.

Transação ocorreu antes do período de validade do registro?

Transação ocorreu antes do período de validade do registro?

O que é engraçado, pois você tem um registro que ainda não é válido no tempo. Ele só se tornará válido – i.e. possível de ser alterado – quando o valor da coluna “inicio_registro” (neste caso) for igual ou menor que o momento da alteração. Na minha humilde opinião, esta verificação – “inicio_registro” é menor ou igual ao momento atual – deveria estar inclusa na opção padrão que é DATA_CONSISTENCY_CHECK = ON. No entanto, para a tabela principal, esta opção apenas verifica se “inicio_registro” é menor ou igual à “fim_registro”. O que você acha?

Já o valor para a coluna “fim_registro” deveria definir uma “validade infinita”. Logo, o valor máximo do tipo DATETIME2 é um bom candidato ao nosso “infinito”. Isso não é obrigatório, claro. Só basta ser maior que a coluna “inicio_registro”. Obviamente, isso pode gerar outras situações inesperadas como registros da tabela principal que já “perderam a validade”. Desta forma, caso você faça uma consulta temporal para o momento atual, o registro que está presente na tabela principal não aparecerá. Crazy, huh?! Falarei sobre diversas situações inusitadas e até um possível bug num outro post. Então, de volta ao assunto!

Caso você possua uma VLT (Very Large Table), essa solução das default constraints pode não ser ideal. Então, outra opção seria criar as colunas de validade do registro como DATETIME2 NULL e atualizar os registros em lotes até que todas estejam com os valores devidos. Depois bastaria alterar a coluna com as opções necessárias e voilà. No entanto, esta opção não é permitida (por enquanto, eu espero). Caso você tente, receberá algo como a mensagem abaixo (clique para aumentar).

No alter column for you...

No alter column for you…

É claro que existem outras maneiras de fazer isso. Uma solução é criar uma tabela vazia com as colunas da tabela existente e todos os outros pré-requisitos de uma tabela temporal, ativar o SYSTEM_VERSIONING e transferir os dados aos poucos de uma tabela para outra enquanto uma view abstrai esta mudança para a aplicação. Obviamente, existem diversas outras formas, mas o ponto é que nada é realmente simples quando se trabalha com VLTs, certo?

Após sanarmos os pré-requisitos na tabela principal, podemos habilitar o SYSTEM_VERSIONING finalmente.

ALTER TABLE [dbo].[Disciplina] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DisciplinaHistorico]));

Caso queira verificar a documentação oficial, basta acessar o artigo do BOL ALTER TABLE (Transact-SQL) [3]. Nota: Caso você não defina uma HISTORY_TABLE, o SQL Server criará uma para você com o nome “MSSQL_TemporalHistoryFor_<object id da tabela principal>”.

Se olharmos agora para o object explorer, veremos o seguinte:

Uuuhhh... New stuff!

Uuuhhh… New stuff!

Verificando a sys.tables (Transact-SQL) [4], vemos três colunas novas: temporal_type, temporal_type_desc e history_table_id. Esta última ainda não aparece na documentação oficial, mas representa a conexão entre a tabela principal e a tabela de histórico.

imagem04

Os valores da coluna temporal_type  também podem ser retornados usando a nova propriedade “TableTemporalType” na função OBJECTPROPERTY (Transact-SQL) [5].

Update: A função OBJECTPROPERTYEX também possui este novo parâmetro.

imagem05

Existem colunas novas também na sys.columns (Transact-SQL) [6] específicas para tabelas temporais.

imagem06

De forma análoga a função OBJECTPROPERTY, podemos obter essas informações através da COLUMNPROPERTY (Transact-SQL) [7].

imagem07

Existe também a nova view de sistema, sys.periods (Transact-SQL) [8], que contém informações sobre o período de validade do registro como quais colunas que definem o período e para qual objeto.

imagem08

CONCLUSÃO

Esta nova feature do SQL Server 2016, System-Versioned Temporal Tables, certamente é bem bacana! As possibilidades de aplicação desta funcionalidade são inúmeras e é ótimo vê-la nesta nova versão. No próximo post falarei sobre alterações de schema em tabelas temporais e estratégias de indexação. Então, até lá! Se quiser mais material publicado sobre o assunto, veja os links na seção “Leitura Adicional”.

REFERÊNCIAS / LINKS

[1] Temporal Tables (https://msdn.microsoft.com/en-us/library/dn935015.aspx)
[2] CREATE TABLE (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms174979.aspx)
[3] ALTER TABLE (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms190273.aspx)
[4] sys.tables (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms187406.aspx)
[5] OBJECTPROPERTY (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms176105.aspx)
[6] sys.columns (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms176106.aspx)
[7] COLUMNPROPERTY (Transact-SQL) (https://msdn.microsoft.com/en-us/library/ms174968.aspx)
[8] sys.periods (Transact-SQL) (https://msdn.microsoft.com/en-us/library/mt130702.aspx) 

LEITURA ADICIONAL

[1] First Look at System-Versioned Temporal Tables-Part 1: Creating Tables and Modifying Data (http://sqlmag.com/sql-server/first-look-system-versioned-temporal-tables-part-1-creating-tables-and-modifying-data)
[2] First Look at System-Versioned Temporal Tables-Part 2: Querying Data and Optimization Considerations (http://sqlmag.com/sql-server/first-look-system-versioned-temporal-tables-part-2-querying-data-and-optimization-conside)
[3] Introduction to SQL Server 2016 Temporal Tables (https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/)
[4] SQL Server 2016 T-SQL Syntax to Query Temporal Tables (https://www.mssqltips.com/sqlservertip/3682/sql-server-2016-tsql-syntax-to-query-temporal-tables/)

Anúncios

2 pensamentos sobre “Tabelas Temporais – Parte 1: Criação de tabelas

  1. Pingback: Tabelas Temporais – Parte 2: Alteração de schema e Indexação | Comunidade SQL Server

  2. Pingback: Retrospectiva 2016 – Comunidade SQL Server

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s