Scénáře použití dočasných tabulek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Dočasné tabulky s systémovou verzí jsou užitečné ve scénářích, které vyžadují sledování historie změn dat. Doporučujeme zvážit dočasné tabulky v následujících případech použití, abyste měli větší výhody produktivity.

Audit dat

Dočasné verze systému můžete použít u tabulek, které ukládají důležité informace, sledovat, co se změnilo a kdy, a provádět forenzní data v jakémkoli okamžiku.

Dočasné tabulky umožňují plánovat scénáře auditu dat v počátečních fázích vývojového cyklu nebo přidávat auditování dat do stávajících aplikací nebo řešení, pokud je potřebujete.

Následující diagram znázorňuje tabulku Employee s ukázkou dat včetně aktuální (označené modrou barvou) a historických verzí řádků (označených šedou barvou).

Pravá část diagramu vizualizuje verze řádků na časové ose a řádky, které vyberete s různými typy dotazování v dočasné tabulce, s klauzulí SYSTEM_TIME nebo bez této klauzule.

Povolení správy systémových verzí v nové tabulce pro audit dat

Pokud identifikujete informace, které potřebují auditování dat, vytvořte databázové tabulky jako dočasné tabulky se systémovou verzí. Následující příklad znázorňuje scénář s tabulkou s názvem Employee v hypotetické databázi personálního oddělení:

CREATE TABLE Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT 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.EmployeeHistory));

Různé možnosti vytvoření dočasné tabulky verze systému jsou popsány v Vytvoření dočasné tabulky verze systému.

Povolení správy systémových verzí u existující tabulky pro audit dat

Pokud potřebujete provést audit dat v existujících databázích, použijte ALTER TABLE k rozšíření ne dočasných tabulek, aby se staly systémovou verzí. Aby vaše aplikace nepodléhala nekompatibilním změnám, přidejte časové sloupce jako HIDDEN, jak je vysvětleno v tématu Vytvoření systémově verzované časové tabulky.

Následující příklad ukazuje povolení správy systémových verzí u existující tabulky Employee v hypotetické databázi personálního oddělení. Umožňuje správu verzí systému v tabulce Employee ve dvou krocích. Nejprve se nové sloupce období přidají ve formátu HIDDEN. Pak vytvoří výchozí tabulku historie.

ALTER TABLE Employee ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Důležitý

Přesnost datového typu datetime2 musí být stejná ve zdrojové tabulce jako v tabulce historie systémových verzí.

Po spuštění předchozího skriptu se všechny změny dat budou transparentně shromažďovat v tabulce historie. V typickém scénáři auditu dat byste se dotazovali na všechny změny dat, které byly použity na jednotlivý řádek v období vašeho zájmu. Výchozí tabulka historie se vytvoří s clusterovaným řádkově uloženým B-stromem, který efektivně řeší tento případ použití.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

Provedení analýzy dat

Jakmile povolíte správu verzí systému pomocí některého z předchozích přístupů, k auditování dat stačí jeden jediný dotaz. Následující dotaz vyhledá verze řádků pro záznamy v tabulce Employee s EmployeeID = 1000, které byly aktivní alespoň pro část období mezi 1. lednem 2021 a 1. lednem 2022 (včetně horní hranice):

SELECT * FROM Employee
FOR SYSTEM_TIME BETWEEN '2021-01-01 00:00:00.0000000'
    AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Nahraďte FOR SYSTEM_TIME BETWEEN...ANDFOR SYSTEM_TIME ALL k analýze celé historie změn dat pro daného zaměstnance:

SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Chcete-li vyhledat verze řádků, které byly aktivní pouze v období (a ne mimo ni), použijte CONTAINED IN. Tento dotaz je efektivní, protože se dotazuje pouze na tabulku historie:

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN (
    '2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000'
)
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

Nakonec v některých scénářích auditu můžete chtít zjistit, jak celá tabulka vypadala v libovolném časovém okamžiku v minulosti:

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00.0000000';

Dočasné tabulky založené na systémové verzi ukládají hodnoty pro sloupce období v časovém pásmu UTC, ale může být pohodlnější pracovat v místním časovém pásmu, a to jak pro filtrování dat, tak zobrazení výsledků. Následující ukázka kódu ukazuje, jak použít podmínku filtrování, která je zadána v místním časovém pásmu a pak převedena na UTC pomocí AT TIME ZONE, který byl zaveden v SQL Serveru 2016 (13.x):

/* Add offset of the local time zone to current time*/
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, - 9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
    [Name],
    Position,
    Department,
    [Address],
    [AnnualSalary],
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
    ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee
FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

Použití AT TIME ZONE je užitečné ve všech ostatních scénářích, ve kterých se používají tabulky se systémovou verzí.

Podmínky filtrování uvedené v časových klauzulích s FOR SYSTEM_TIME jsou SARG-ovatelné. SARG znamená argument hledánía SARG-able znamená, že SQL Server může použít základní clusterovaný index k provedení hledání namísto operace prohledávání. Další informace naleznete v tématu SQL Server Index Architecture and Design Guide.

Pokud dotazujete tabulku historie přímo, ujistěte se, že je podmínka filtrování také SARG schopná zadáním filtrů ve formě <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'.

Pokud použijete AT TIME ZONE na sloupce období, SQL Server provede kontrolu tabulky nebo indexu, což může být velmi nákladné. Vyhněte se tomuto typu podmínky v dotazech:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition.

Další informace najdete v tématu Dotazování dat v časové tabulce s verzováním podle systému.

Analýza k určitému bodu v čase (časová cesta)

Místo toho, abyste se zaměřili na změny jednotlivých záznamů, ukazují scénáře cesty k času, jak se v průběhu času mění celé sady dat. Někdy cestování časem zahrnuje několik souvisejících časových tabulek, přičemž každá se mění nezávislým tempem, které si přejete analyzovat.

• Trendy důležitých ukazatelů v historických a aktuálních datech
• Přesný okamžitý přehled veškerých dat k libovolnému okamžiku v čase v minulosti (včera, před měsícem atd.)
• Rozdíly mezi dvěma body v čase zájmu (před měsícem vs. před třemi měsíci, například)

Existuje mnoho scénářů z reálného světa, které vyžadují analýzu časových cest. Pro ilustraci tohoto scénáře použití se podíváme na OLTP s automaticky vygenerovanou historií.

OLTP s automaticky vygenerovanou historií dat

V systémech zpracování transakcí můžete analyzovat, jak se v průběhu času mění důležité metriky. V ideálním případě by analýza historie neměla ohrozit výkon aplikace OLTP, kde musí dojít k přístupu k nejnovějšímu stavu dat s minimální latencí a uzamčením dat. Dočasné tabulky s systémovou verzí můžete použít k transparentnímu zachování úplné historie změn pro pozdější analýzu odděleně od aktuálních dat s minimálním dopadem na hlavní úlohu OLTP.

U úloh s vysokým transakčním zpracováním v SQL Serveru a azure SQL Managed Instance doporučujeme používat dočasné tabulky systémem s tabulkami optimalizovanými pro paměť, které umožňují ukládat aktuální data do paměti a úplnou historii změn na disku nákladově efektivním způsobem.

Pro tabulku historie doporučujeme použít clusterovaný index columnstore z následujících důvodů:

• Typická analýza trendu přináší výhody z výkonu dotazů poskytovaných clusterovaným indexem columnstore.

• Úloha pro vyprázdnění dat s tabulkami optimalizovanými pro paměť dosahuje nejlepšího výkonu při silné zátěži OLTP, pokud má tabulka historie clusterovaný index columnstore.

• Clusterovaný index columnstore poskytuje vynikající kompresi, zejména ve scénářích, kdy se ve stejnou dobu nezmění všechny sloupce.

Použití dočasných tabulek s OLTP v paměti snižuje potřebu zachovat celou datovou sadu v paměti a umožňuje snadno rozlišit mezi horkými a studenými daty.

Příklady scénářů z reálného světa, které se dobře hodí do této kategorie, jsou mimo jiné správa zásob nebo obchodování s měnou.

Následující diagram znázorňuje zjednodušený datový model používaný ke správě inventáře:

Následující příklad kódu vytvoří tabulku ProductInventory jako systémově verzovanou dočasnou tabulku v paměti s clusterovaným indexem columnstore v tabulce historie (který nahradí index úložiště řádků, jenž je vytvořen standardně):

Poznámka

Ujistěte se, že vaše databáze umožňuje vytvářet tabulky optimalizované pro paměť. Viz Vytvoření tabulky Memory-Optimized a nativně zkompilované uložené procedury.

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
        SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory] (
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

U předchozího modelu to znamená, že postup údržby inventáře může vypadat takto:

CREATE PROCEDURE [dbo].[spUpdateInventory]
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    UPDATE dbo.ProductInventory
    SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location

    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

Uložená procedura spUpdateInventory vloží nový produkt do inventáře nebo aktualizuje množství produktů pro konkrétní umístění. Obchodní logika je jednoduchá a zaměřuje se na udržení co nejpřesnějšího aktuálního stavu tím, že se pole Quantity zvyšuje nebo snižuje prostřednictvím aktualizací tabulek, zatímco systémově verzované tabulky přidávají k datům historickou dimenzi, jak je ukázáno na níže uvedeném diagramu.

Nyní je možné efektivně provádět dotazování nejnovějšího stavu z nativně zkompilovaného modulu:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    SELECT ProductId, LocationID, Quantity, ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId
END;
GO

EXEC [dbo].[spQueryInventoryLatestState];

Analýza změn dat v průběhu času se usnadňuje pomocí klauzule FOR SYSTEM_TIME ALL, jak je znázorněno v následujícím příkladu:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory
AS
SELECT ProductId,
    LocationId,
    Quantity,
    ValidFrom,
    ValidTo
FROM [dbo].[ProductInventory]
FOR SYSTEM_TIME ALL;
GO

SELECT * FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

Následující diagram znázorňuje historii dat pro jeden produkt, který se dá snadno vykreslit při importu předchozího zobrazení v Power Query, Power BI nebo podobném nástroji business intelligence:

Dočasné tabulky lze v tomto scénáři použít k provádění jiných typů analýzy časových cest, jako je například rekonstrukce stavu inventáře AS OF libovolného bodu v čase v minulosti nebo porovnání snímků, které patří k různým momentům v čase.

Pro tento scénář použití můžete také rozšířit tabulky Product a Location, aby se z nich staly temporální tabulky, což umožní pozdější analýzu historie změn UnitPrice a NumberOfEmployee.

ALTER TABLE Product ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location] ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DFValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE [Location]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

Vzhledem k tomu, že datový model teď zahrnuje několik dočasných tabulek, osvědčeným postupem pro analýzu AS OF je vytvořit zobrazení, které extrahuje potřebná data ze souvisejících tabulek a použije FOR SYSTEM_TIME AS OF na zobrazení, protože to výrazně zjednodušuje rekonstruování stavu celého datového modelu:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
    PrInv.LocationId,
    P.ProductName,
    L.LocationName,
    PrInv.Quantity,
    P.UnitPrice,
    L.NumberOfEmployees,
    P.ValidFrom AS ProductStartTime,
    P.ValidTo AS ProductEndTime,
    L.ValidFrom AS LocationStartTime,
    L.ValidTo AS LocationEndTime,
    PrInv.ValidFrom AS InventoryStartTime,
    PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
INNER JOIN dbo.Product AS P
    ON PrInv.ProductId = P.ProductID
INNER JOIN dbo.Location AS L
    ON PrInv.LocationId = L.LocationID;
GO

SELECT * FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF '2022-01-01';

Následující snímek obrazovky ukazuje plán provádění vygenerovaný pro dotaz SELECT. To ilustruje, že databázový stroj zpracovává veškerou složitost při práci s dočasnými relacemi:

Pomocí následujícího kódu můžete porovnat stav inventáře produktů mezi dvěma body v čase (před dnem a před měsícem):

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo DATETIME2 = DATEADD (MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
    inventoryDayAgo.ProductName,
    inventoryDayAgo.LocationName,
    inventoryDayAgo.Quantity AS QuantityDayAgo,
    inventoryMonthAgo.Quantity AS QuantityMonthAgo,
    inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
    inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
INNER JOIN vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

Detekce anomálií

Detekce anomálií (neboli detekce odlehlých hodnot) je identifikace položek, které neodpovídají očekávanému vzoru nebo jiným položkám v datové sadě. Dočasné tabulky s systémovou verzí můžete použít k detekci anomálií, ke kterým dochází pravidelně nebo nepravidelně, protože k rychlému vyhledání konkrétních vzorů můžete využít dočasné dotazování. Jaká anomálie závisí na typu dat, která shromažďujete, a na obchodní logice.

Následující příklad ukazuje zjednodušenou logiku pro detekci "špiček" v prodejních číslech. Předpokládejme, že pracujete s dočasnou tabulkou, která shromažďuje historii zakoupených produktů:

CREATE TABLE [dbo].[Product] (
    [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] [varchar](100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

Následující diagram znázorňuje nákupy v průběhu času:

Za předpokladu, že v pravidelných dnech má počet zakoupených produktů malý rozptyl, následující dotaz identifikuje odlehlé hodnoty: vzorky, které rozdíly ve srovnání s jejich bezprostředními sousedy jsou významné (2x), zatímco okolní vzorky se výrazně neliší (méně než 20%):

WITH CTE (
    ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo
    )
AS (
    SELECT ProdId,
        LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
        DailySales,
        LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
        ValidFrom,
        ValidTo
    FROM Product
    FOR SYSTEM_TIME ALL
    )
SELECT ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo,
    ABS(PrevValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN NextValue > PrevValue THEN PrevValue
                ELSE NextValue
            END)) AS PrevToNextDiff,
    ABS(CurrentValue - PrevValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > PrevValue THEN PrevValue
                ELSE CurrentValue
            END)) AS CurrentToPrevDiff,
    ABS(CurrentValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > NextValue THEN NextValue
                ELSE CurrentValue
            END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (
        CASE
            WHEN NextValue > PrevValue THEN PrevValue
            ELSE NextValue
        END) < 0.2
    AND ABS(CurrentValue - PrevValue) / (
        CASE
            WHEN CurrentValue > PrevValue THEN PrevValue
            ELSE CurrentValue
        END) > 2
    AND ABS(CurrentValue - NextValue) / (
        CASE
            WHEN CurrentValue > NextValue THEN NextValue
            ELSE CurrentValue
        END) > 2;

Poznámka

Tento příklad je záměrně zjednodušený. V produkčních scénářích byste pravděpodobně použili pokročilé statistické metody k identifikaci vzorků, které nepoužívají běžný vzor.

Pomalu se měnící dimenze

Dimenze v datových skladech obvykle obsahují relativně statická data o entitách, jako jsou geografická umístění, zákazníci nebo produkty. Některé scénáře však vyžadují sledování změn dat v tabulkách dimenzí. Vzhledem k tomu, že změny dimenzí probíhají mnohem méně často, nepředvídatelným způsobem a mimo plán pravidelné aktualizace, který se vztahuje na tabulky faktů, se tyto typy tabulek dimenzí nazývají pomalu se měnící dimenze (SCD).

Existuje několik kategorií pomalu se měnících dimenzí na základě toho, jak se zachová historie změn:

Typ dimenze	Podrobnosti
Typ 0	Historie se nezachová. Atributy dimenze odrážejí původní hodnoty.
Typ 1	Atributy dimenze odrážejí nejnovější hodnoty (předchozí hodnoty jsou přepsány)
typ 2	Každá verze členu dimenze reprezentovaná samostatným řádkem v tabulce obvykle se sloupci, které představují období platnosti
typ 3	Zachování omezené historie pro vybrané atributy pomocí nadbytečných sloupců na stejném řádku
Typ 4	Zachování historie v samostatné tabulce, zatímco původní tabulka dimenzí uchovává nejnovější (aktuální) verze členů dimenze

Když zvolíte strategii SCD, je zodpovědností vrstvy ETL (Extract-Transform-Load) udržovat tabulky dimenzí přesné, což obvykle vyžaduje složitější kód a dodatečnou údržbu.

Dočasné tabulky s systémovou verzí je možné použít k výraznému snížení složitosti kódu, protože historie dat se automaticky zachová. Vzhledem k implementaci pomocí dvou tabulek jsou dočasné tabulky nejblíže scD typu 4. Vzhledem k tomu, že dočasné dotazy umožňují odkazovat pouze na aktuální tabulku, můžete také zvážit dočasné tabulky v prostředích, kde plánujete použít scD typu 2.

Chcete-li převést běžnou dimenzi na SCD, můžete vytvořit novou nebo změnit existující dimenzi tak, aby se stala dočasnou tabulkou se systémovou verzí. Pokud existující tabulka dimenzí obsahuje historická data, vytvořte samostatná tabulka a přesuňte historická data tam a ponechte aktuální (skutečné) verze dimenzí v původní tabulce dimenzí. Potom pomocí syntaxe ALTER TABLE převeďte tabulku dimenzí na časovou tabulku verziovanou systémem s předdefinovanou tabulkou historie.

Následující příklad ukazuje proces a předpokládá, že tabulka dimenzí DimLocation již má ValidFrom a ValidTo jako datetime2 sloupce bez hodnoty null, které jsou naplněny procesem ETL:

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

Po vytvoření není při načítání datového skladu potřeba žádný další kód k údržbě SCD.

Následující obrázek ukazuje, jak můžete použít dočasné tabulky v jednoduchém scénáři zahrnujícím dva identifikátory SCD (DimLocation a DimProduct) a jednu tabulku faktů.

Aby bylo možné používat starší verze dat SCD v sestavách, musíte efektivně upravit dotazování. Můžete například chtít vypočítat celkovou částku prodeje a průměrný počet prodaných produktů na obyvatele za posledních šest měsíců. Obě metriky vyžadují korelaci dat z tabulky faktů a dimenzí, které mohly změnit jejich atributy důležité pro analýzu (DimLocation.NumOfCustomers, DimProduct.UnitPrice). Následující dotaz správně vypočítá požadované metriky:

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo DATETIME2;

SET @sixMonthsAgo = DATEADD(month, - 12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
    DimLocation_History.LocationId,
    SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
    AVG(F.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimLocation
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
    ON DimLocation_History.LocationId = F.LocationId
    AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimProduct
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
    ON DimProduct_History.ProductId = F.ProductId
    AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Úvahy

Použití časově verzí systémových tabulek pro SCD je přijatelné, pokud je období platnosti vypočítané na základě času databázové transakce v souladu s obchodní logikou. Pokud načtete data s významným zpožděním, nemusí být doba transakce přijatelná.

Dočasné tabulky s systémovou verzí ve výchozím nastavení neumožňují po načtení měnit historická data (po nastavení SYSTEM_VERSIONING na OFFmůžete změnit historii). Může se jednat o omezení v případech, kdy se pravidelně mění historická data.

Systémově verzované časové tabulky generují verzi řádku při jakékoli změně sloupce. Pokud chcete potlačit nové verze u určité změny sloupce, musíte toto omezení začlenit do logiky ETL.

Pokud očekáváte velký počet historických řádků v tabulkách SCD, zvažte použití clusterovaného indexu columnstore jako hlavní možnosti úložiště pro tabulku historie. Použití indexu columnstore snižuje nároky na tabulku historie a zrychluje analytické dotazy.

Oprava poškození dat na úrovni řádků

Můžete se spolehnout na historická data v časových tabulkách se systémovými verzemi a rychle obnovit jednotlivé řádky na libovolný z dříve zachycených stavů. Tato vlastnost dočasných tabulek je užitečná, když můžete najít ovlivněné řádky nebo když znáte čas změny nežádoucích dat. Tyto znalosti umožňují efektivně provádět opravy bez nutnosti provádět zálohování.

Tento přístup má několik výhod:

• Můžete přesně řídit rozsah opravy. Záznamy, které nejsou ovlivněné, musí zůstat v nejnovějším stavu, což je často kritický požadavek.

• Operace je efektivní a databáze zůstane online pro všechny úlohy, které data používají.

• Samotná operace opravy je verzována. Máte záznam auditu pro samotnou operaci opravy, abyste mohli v případě potřeby analyzovat, co se stalo později.

Akce opravy se dá automatizovat relativně snadno. Následující příklad kódu ukazuje uloženou proceduru, která provádí opravu dat pro tabulku Employee použitá ve scénáři auditu dat.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord
    @EmployeeID INT,
    @versionNumber INT = 1
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmployeeID
            ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID
)
/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

Tato uložená procedura přebírá @EmployeeID a @versionNumber jako vstupní parametry. Tento postup ve výchozím nastavení obnoví stav řádku na poslední verzi z historie (@versionNumber = 1).

Následující obrázek znázorňuje stav řádku před a po volání procedury. Červený obdélník označuje aktuální verzi řádku, která je nesprávná, zatímco zelený obdélník označuje správnou verzi z historie.

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1;

Tuto uloženou proceduru opravy lze nastavit tak, aby přijímala přesné časové razítko místo verze řádku. Obnoví řádek na libovolnou verzi, která byla aktivní pro poskytnutý bod v čase (to znamená AS OF pro daný bod v čase).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf
    @EmployeeID INT,
    @asOf DATETIME2
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
SET [Position] = History.[Position],
    [Department] = History.Department,
    [Address] = History.[Address],
    AnnualSalary = History.AnnualSalary
FROM Employee AS E
INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
    ON E.EmployeeID = History.EmployeeID
WHERE E.EmployeeID = @EmployeeID;

Pro stejnou ukázku dat znázorňuje následující obrázek opravu s časovou podmínkou. Zvýrazněné jsou parametr @asOf, vybraný řádek v historii, který byl skutečný v zadaném okamžiku v čase, a nová verze řádku v aktuální tabulce po operaci opravy:

Oprava dat se může stát součástí automatizovaného načítání dat v systémech datových skladů a reportingových systémů. Pokud nově aktualizovaná hodnota není správná, pak je v mnoha scénářích obnovení předchozí verze z historie dostatečným opatřením. Následující diagram znázorňuje, jak lze tento proces automatizovat:

Související obsah

• dočasných tabulek
• Začínáme s časovými tabulkami podle verze systému
• kontroly konzistence systému časových tabulek
• Oddíl s dočasnými tabulkami
• úvahy a omezení časových tabulek
• zabezpečení časových tabulek
• Systémově verzované časové tabulky s tabulkami optimalizovanými pro paměť
• Zobrazení metadat časových tabulek a funkcí