Dočasné tabulky

Platí na: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Časové tabulky (také známé jako systémově verzované časové tabulky) jsou databázová funkce, která poskytuje vestavěnou podporu informací o datech uložených v tabulce v jakémkoli okamžiku, nikoli pouze o aktuálních datech.

Začínáme se systémovými dočasnými tabulkamia projděte si scénáře použití dočasných tabulek.

Co je systémově verzovaná časová tabulka?

Systemově verzovaná časová tabulka je typ uživatelské tabulky navržené tak, aby uchovávala úplnou historii změn dat, což umožňuje analýzu v jednotlivých bodech času. Tento typ časové tabulky se nazývá systémově verzovaná časová tabulka, protože systém spravuje období platnosti pro každý řádek (tedy Database Engine).

Každá dočasná tabulka má dva explicitně definované sloupce, každý s datovým typem datetime2. Tyto sloupce se nazývají periodické sloupce. Database Engine používá tyto sloupce s periodickými intervaly výhradně k zaznamenání období platnosti každého řádku pokaždé, když je řádek upraven. Hlavní tabulka, která ukládá aktuální data, se nazývá aktuální tabulka nebo časová tabulka.

Kromě těchto sloupců období obsahuje dočasná tabulka také odkaz na jinou tabulku se zrcadleným schématem označovaným jako tabulka historie . Systém používá tabulku historie k automatickému uložení předchozí verze řádku při každé aktualizaci nebo odstranění řádku v dočasné tabulce. Během vytváření dočasné tabulky můžete zadat existující tabulku historie (která musí vyhovovat schématu) nebo nechat systém vytvořit výchozí tabulku historie.

Proč časová?

Skutečné zdroje dat jsou dynamické a obchodní rozhodnutí často vycházejí z poznatků, které analytici získávají z vývoje dat. Mezi případy použití dočasných tabulek patří:

  • Auditování všech změn dat a provádění forenzních dat v případě potřeby
  • Rekonstrukce stavu dat v jakémkoli okamžiku v minulosti
  • Výpočet trendů v průběhu času
  • Udržování pomalu se měnící dimenze pro aplikace podporující rozhodování
  • Obnovení z náhodných změn dat a chyb aplikace

Jak dočasná práce funguje?

Správa systémových verzí pro tabulku se implementuje jako dvojice tabulek: aktuální tabulka a tabulka historie. V každé z těchto tabulek jsou dva další sloupce datetime2 , které definují období platnosti pro každý řádek:

  • počáteční sloupec období: Systém zaznamenává čas zahájení řádku v tomto sloupci, obvykle označený jako sloupec ValidFrom.

  • Sloupec konce období : Systém obvykle zaznamenává koncový čas řádku v tomto sloupci, který bývá označen jako sloupec ValidTo.

Aktuální tabulka obsahuje aktuální hodnotu pro každý řádek. Tabulka historie obsahuje každou předchozí hodnotu (starou verzi) pro každý řádek, pokud existuje, a počáteční a koncový čas pro období, pro které byla platná.

Diagram znázorňující, jak funguje dočasná tabulka

Následující skript znázorňuje scénář s informacemi o zaměstnancích:

CREATE TABLE dbo.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 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Další informace najdete v tématu Vytvoření systémově verzované časové tabulky.

  • Vložení: Systém nastaví pro sloupec ValidFrom hodnotu na čas zahájení aktuální transakce (v časovém pásmu UTC) podle systémových hodin a sloupci ValidTo přiřadí maximální hodnotu 9999-12-31. Tím se řádek označí jako otevřený.

  • Aktualizace: Systém ukládá předchozí hodnotu řádku do tabulky historie a nastavuje hodnotu sloupce ValidTo na začátek aktuální transakce (v UTC časovém pásmu) na základě systémových hodin. Tím se řádek označí jako uzavřený, přičemž je zaznamenáno časové období, po které byl řádek platný. V aktuální tabulce se řádek aktualizuje o novou hodnotu a systém nastaví hodnotu pro sloupec ValidFrom na počáteční čas transakce (v časovém pásmu UTC) na základě systémových hodin. Hodnota aktualizovaného řádku v aktuální tabulce pro sloupec ValidTo zůstává maximální hodnotou 9999-12-31.

  • Mazání: Systém ukládá předchozí hodnotu řádku do tabulky historie a nastavuje hodnotu ValidTo sloupce na začátek aktuální transakce (v UTC časovém pásmu) na základě systémových hodin. Tím se řádek označí jako uzavřený, s časovým obdobím, pro které byl platný předchozí řádek. V aktuální tabulce se řádek odebere. Dotazy aktuální tabulky nevracely tento řádek. Pouze dotazy, které pracují s daty historie, vrací data, pro která je řádek zavřený.

  • Merge: Operace se chová přesně tak, jako by byly vykonány až tři příkazy (, INSERT, a UPDATE/nebo a DELETE), v závislosti na tom, co je v příkazu MERGE uvedeno jako akce.

Časy zaznamenané v systému datetime2 sloupce jsou založeny na počátečním čase samotné transakce. Například všechny řádky vložené do jedné transakce mají stejný čas UTC zaznamenaný ve sloupci odpovídající začátku SYSTEM_TIME období.

Když spustíte dotazy na úpravy dat v dočasné tabulce, databázový stroj přidá do tabulky historie řádek, i když se nezmění žádné hodnoty sloupců.

Jak se můžu dotazovat na dočasná data?

Příkaz SELECT ... FROM <table> má novou klauzuli FOR SYSTEM_TIMEs pěti dílčími klauzulemi zaměřenými na dotazování dat v aktuální a historické tabulky. Tato nová syntaxe příkazu SELECT se podporuje přímo na jedné tabulce, prostřednictvím více spojení a pomocí zobrazení na několika temporálních tabulkách.

Když použijete klauzuli FOR SYSTEM_TIME s jednou z pěti podvětí v dotazu, výsledky obsahují historická data z časové tabulky, jak je ukázáno na následujícím obrázku.

Diagram ukazující, jak funguje časové dotazování.

Následující dotaz vyhledá verze řádků pro zaměstnance s podmínkou filtru WHERE EmployeeID = 1000, které byly aktivní alespoň po část období od 1. ledna 2021 do 1. ledna 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;

FOR SYSTEM_TIME vyfiltruje řádky, které mají období platnosti s nulovou dobou trvání (ValidFrom = ValidTo).

Database Engine tyto řádky generuje, pokud provedete více aktualizací na stejném primárním klíči ve stejné transakci. V takovém případě dočasné dotazování vrátí pouze verze řádků před transakcemi a aktuální řádky za transakcemi.

Pokud potřebujete zahrnout tyto řádky do analýzy, dotazujte se přímo na tabulku historie.

V následující tabulce ValidFrom ve sloupci Opravňující řádky představuje hodnotu v ValidFrom sloupci v dotazované tabulce a ValidTo představuje hodnotu ve sloupci ValidTo v dotazované tabulce. Úplnou syntaxi a příklady najdete v tématu KLAUZULE FROM a JOIN, APPLY, PIVOTa Dotazování dat v systémově verzované temporální tabulce.

Expression Kvalifikující řádky Note
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time Vrátí tabulku s řádky obsahujícími hodnoty, které byly aktuální v zadaném bodu v čase v minulosti. Interně se provádí sjednocení mezi dočasnou tabulkou a její tabulkou historie. Výsledky jsou filtrovány tak, aby vracely hodnoty v řádku, které byly platné v okamžiku v čase zadaném parametrem date_time. Hodnota řádku se považuje za platnou, pokud je hodnota system_start_time_column_name menší nebo rovna hodnotě parametru date_time a hodnota system_end_time_column_name je větší než hodnota parametru date_time.
FROM start_date_timeTOend_date_time ValidFrom < konec_časuAND ValidTo >začátek_času Vrátí tabulku s hodnotami pro všechny verze řádků, které byly aktivní v zadaném časovém rozsahu, bez ohledu na to, jestli začaly být aktivní před hodnotou parametru start_date_time pro argument FROM nebo přestaly být aktivní po hodnotě parametru end_date_time pro argument TO. Interně se provádí sjednocení mezi dočasnou tabulkou a její tabulkou historie. Výsledky jsou filtrovány tak, aby vracely hodnoty pro všechny verze řádků, které byly aktivní kdykoli během zadaného časového rozsahu. Řádky, které přestaly být aktivní přesně na dolní hranici definované koncovým bodem FROM, nejsou zahrnuté a záznamy, které se staly aktivní přesně na horní hranici definované koncovým bodem TO, se také nezahrnou.
BETWEEN start_date_timeANDend_date_time ValidFrom <= konec_časuAND ValidTo >začátek_času Stejné jako předchozí v popisu FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time, s výjimkou toho, že tabulka vrácených řádků obsahuje řádky, které se staly aktivní na horní hranici, která byla definována koncovým bodem end_date_time.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_timeAND ValidTo <=end_date_time Vrátí tabulku s hodnotami pro všechny verze řádků, které byly otevřeny a uzavřeny v zadaném časovém rozsahu definovaném dvěma hodnotami období pro argument CONTAINED IN. Řádky, které byly aktivní přesně na dolní hranici nebo přestaly být aktivní přesně na horní hranici, jsou zahrnuty.
ALL Všechny řádky Vrátí sjednocení řádků, které patří jak do aktuální tabulky, tak do tabulky historie.

Skrýt sloupce období

Můžete skrýt sloupce období, takže dotazy SELECT, které na ně výslovně neodkazují, tyto sloupce nevracejí, například SELECT * FROM <table>.

Pokud chcete vrátit skrytý sloupec, musíte explicitně odkazovat na skrytý sloupec v dotazu. Podobně INSERT a BULK INSERT příkazy pokračují, jako by tyto nové sloupce období nebyly přítomny (a hodnoty sloupců se vyplní automaticky).

Podrobnosti o použití HIDDEN klauzule naleznete CREATE TABLE a ALTER TABLE.

Samples

Související obsah

  • Last updated on