24. února 2016

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

2 komentáře:

  1. jakým způsobem záznamy verzovat ještě společně s ID uživatele, který změnu provedl?

    OdpovědětVymazat
  2. Nativně ne, ale našel jsem například tento Tip, jak udělat řešení, které loguje i další atributy https://www.mssqltips.com/sqlservertip/4722/auditing-who-changed-temporal-table-data-in-sql-server/

    OdpovědětVymazat