Sdílet prostřednictvím

Dočasné tabulky

  • Článek

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

Dočasné tabulky (označované také jako dočasné tabulky se systémovou verzí) jsou funkce databáze, která poskytuje integrovanou podporu pro poskytování informací o datech uložených v tabulce v libovolném okamžiku, a ne pouze data, která jsou v aktuálním okamžiku v čase správná.

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?

Dočasná tabulka se systémovou verzí je typ uživatelské tabulky navržená tak, aby zachovala úplnou historii změn dat, což umožňuje snadnou analýzu k určitému bodu v čase. Tento typ dočasné tabulky se označuje jako dočasná tabulka se systémovou verzí, protože systém spravuje dobu platnosti pro každý řádek (to znamená databázový stroj).

Každá dočasná tabulka má dva explicitně definované sloupce, každý s datovým typem datetime2. Tyto sloupce se označují jako perioda sloupce. Tyto sloupce období používají výhradně systém k zaznamenávání doby platnosti každého řádku při každé změně řádku. Hlavní tabulka, která ukládá aktuální data, se označuje jako aktuální tabulka, nebo jednoduše jako dočasná 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 častěji než ne obchodní rozhodnutí spoléhají na poznatky, které analytici mohou získat 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
  • Rekonstruování stavu dat kdykoli 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 se k definování doby platnosti pro každý řádek používají dva nadbytečné sloupce datetime2:

  • 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í hodnotu sloupce ValidFrom na počáteční čas aktuální transakce (v časovém pásmu UTC) na základě systémových hodin a přiřadí hodnotu sloupce ValidTo maximální hodnotě 9999-12-31. Tím se řádek označí jako otevřený.

  • Aktualizace: Systém uloží předchozí hodnotu řádku v tabulce historie a nastaví hodnotu pro sloupec ValidTo na počáteční čas aktuální transakce (v časovém pásmu UTC) 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.

  • Odstranění: Systém uloží předchozí hodnotu řádku v tabulce historie a nastaví hodnotu pro sloupec ValidTo na začátek aktuální transakce (v časovém pásmu UTC) 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ý.

  • Sloučení: Operace se chová přesně tak, jako kdyby byly provedeny až tři příkazy (INSERT, UPDATEa/nebo DELETE) v závislosti na tom, co je zadáno jako akce v příkazu MERGE.

Č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.

Při dotazování pomocí klauzule FOR SYSTEM_TIME s použitím jedné z pěti dílčích podmínek se zahrnou historická data z temporální tabulky, jak je znázorněno na následujícím obrázku.

diagram znázorňující, jak funguje dočasné 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).

Tyto řádky se generují, pokud provádíte více aktualizací na stejném primárním klíči v rámci stejné transakce. 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.

Výraz Kvalifikující řádky Poznámka
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_datum_časAND ValidTo >začátek_datum_čas 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 >= začátek_časAND ValidTo <=konec_čas 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í

Sloupce období můžete skrýt tak, aby dotazy, které na ně explicitně neodkazovaly, nevrací tyto sloupce (například při spuštění 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í klauzule HIDDEN naleznete v tématu CREATE TABLE a ALTER TABLE.

Vzorky

Související obsah