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

Novas funções de compressão de dados no SQL Server 2016

Vou iniciar uma série de posts sobre as novas funcionalidades do SQL Server 2016, começando pelas funções de compactação de dados. 

As versões anteriores do SQL Server já traziam opções de compactação de página e linha, habilitadas com CREATE ou ALTER TABLE.

As funções COMPRESS e DECOMPRESS podem ser utilizadas nos comandos INSERT, UPDATE, SELECT para compactar e descompactar coluna por coluna.  O algoritmo utilizado é o GZip (https://en.wikipedia.org/wiki/Gzip), a aplicação pode utilizar o GZip para compactar e enviar para o SQL Server ou então utilizar as funções para compactar direto no T-SQL.

Exemplo

DROP TABLE IF EXISTS Products

CREATE TABLE Products (

 ProductID int,

 Name nvarchar(50),

 ProductModel nvarchar(50),

 Description nvarchar(400),

 Description_COMP varbinary(max))

go

INSERT INTO Products (ProductID,Name,ProductModel,Description,Description_COMP)

select ProductID,Name,ProductModel,Description,COMPRESS(Description) as Description_COMP

from AdventureWorks.Production.vProductAndDescription

SELECT ProductID,Name,ProductModel,Description,

CAST(DECOMPRESS(Description_COMP) AS NVARCHAR(400)) AS Description_COMP

FROM Products

Além das funções COMPRESS e DECOMPRESS o script acima utiliza outra novidade no SQL Server 2016, o DROP ... IF EXISTS, que pode ser utilizado em: AGGREGATE, PROCEDURE, TABLE, ASSEMBLY, TRIGGER, VIEW, TYPE, DATABASE, SCHEMA, USER, DEFAULT, VIEW, FUNCTION, SEQUENCE, INDEX e SYNONYM.

A função COMPRESS retorna VARBINARY, por isso foi utilizada no CREATE TABLE VARBINARY(MAX) na coluna Description_COMP.

Como a função DECOMPRESS também retorna VARBINARY, foi utilizada no último SELECT a função CAST para converter para o tipo de dado original da coluna.

SELECT ProductID,Name,Description,

DATALENGTH(Description) as Tamanho_SemCOMP,

DATALENGTH(Description_COMP) AS Tamanho_COMP

FROM Products

No script acima utilizei a função DATALENGTH, que retorna a quantidade de bytes de uma expressão, para analisar a eficiência da compactação.  Veja que na primeira linha, por ser uma string pequena, a coluna compactada ficou até maior que a original, mas nas demais linhas apresentou ganho.

Até o próximo post.

Saudações Tricolores,

Landry

SQL Server 2014 SP1 BUG - Log Shipping em modo StandBy corrompe Transaction Log

Recentemente passei por um problema sério em um cliente, onde o Transaction Log corrompia intermitentemente no servidor secundário.  Isso me obrigava a refazer a sincronia inicial, isto é, executar um Backup FULL na produção, copiar pela rede (esse cliente tem bancos de 300GB a 700GB) e Restore no servidor secundário.

Recentemente a Microsoft liberou um HotFix para resolver este problema, relatado no artigo KB 2987585:

https://support.microsoft.com/en-us/kb/2987585

O problema é específico para Log Shipping que utiliza a opção Standby, em volumes formatados com modo avançado.  Quem quiser entender o que significa “formatar com modo avançado”, o artigo abaixo explica muito bem:

https://msdn.microsoft.com/en-us/library/windows/desktop/hh848035(v=vs.85).aspx

Para fazer download do HotFix, que está no pacote “Cumulative Update 2” do SQL Server 2014 SP1, basta clicar no link abaixo:

https://support.microsoft.com/en-us/kb/3075950

Até o próximo post.

Saudações Tricolores,

Landry