Temporal tables

Co jsou temporal tables? Jedná se o novinku, která bude dostupná v SQL Serveru 2016 a je také k dispozici v Azure SQL Database jako Preview funkce. O co u temporal tables jde? Jednoduše o verzování dat. Umožňují vytáhnout, jak data vypadaly ve specifickém okamžiku v čase.

Možné aplikace

Verzování veškerých datových změn v tabulce

Správa SCD (Slowly changing dimension) pro podporu rozhodování

Možnost vrátit se k předchozí verzi záznamu/ů při nechtěné změně

Počítání trendů

Z pohledu BI bych vypíchnul první dva body, audit veškerých změn a SCD vestavěným mechanismem bez nutnosti programovat vlastní logiku při vkládání. Tzn pokud chcete SCD 2 na tabulce, kde je temporal table zapnutá. Děláte jen update stávajících záznamů. O SCD se starají skřítci SeTo a Samo :)

Jak to funguje

Kažá temporal table obsahuje dva explicitně definované sloupce typu datetime2, které vy ale neplníte. Při tvorbě tabulky tvoříte referencovanou tabulku pro udržování historie pokud je záznam v temporal table upraven, nebo smazán. Tato tabulka je tvořena buď uživatelem, nebo si systém vytvoří tabulku defaultní.

Příklad

Tvorba temporal table

CREATE TABLE dbo.MY_CUSTOMERS

(

       [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,

       [NameStyle] [dbo].[NameStyle] NOT NULL,

       [Title] [nvarchar](8) NULL,

       [FirstName] [dbo].[Name] NOT NULL,

       [MiddleName] [dbo].[Name] NULL,

       [LastName] [dbo].[Name] NOT NULL,

       [Suffix] [nvarchar](10) NULL,

       [CompanyName] [nvarchar](128) NULL,

       [SalesPerson] [nvarchar](256) NULL,

       [EmailAddress] [nvarchar](50) NULL,

       [Phone] [dbo].[Phone] NULL,

       [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START,

       [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END,

       PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

 ) 

 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MY_CUSTOMERS_HISTORY));

Všimněte si elementů u sloupců validfrom a valito, které se budou starat o udržování historie. System versioning on a název history table se postará o zbytek. Dále jsem naplnil data do tabulky z DB AdventurWorksLT, která je k dispozici v Azure SQL Database jako demo. Po nějaké době jsem změnil emailové adresy vybraných zákazníků. Pokud chci vytáhnout veškeré verze záznamů pro zákazníka 21, mohu to udělat následujícím dotazem

DECLARE @DATEFROM datetime = getdate()-1

DECLARE @DATETO datetime = getdate()

SELECT * FROM dbo.MY_CUSTOMERS

    FOR SYSTEM_TIME 

       FROM @DATEFROM TO @DATETO

WHERE CustomerID = 21

ORDER BY ValidFrom DESC;

For system time from to definuje časové okno ze kterého mě zajímají verze. Pokud mě zajímá poslední verze záznamu k datumu a času

DECLARE @DATETO datetime = getdate()

SELECT * FROM dbo.MY_CUSTOMERS

    FOR SYSTEM_TIME AS OF  @DATETO

WHERE CustomerID = 21

ORDER BY ValidFrom DESC;

Závěr

Temporal tables jsou zajímavá funkce, která zjednodušuje práci s historizací záznamů. Pokud by Vás zajímalo více informací, můžete kouknout na oficiální dokumentaci zde https://msdn.microsoft.com/en-IN/library/dn935015.aspx