Temporala tabeller

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Temporala tabeller (även kända som systemversionerade temporala tabeller) är en databasfunktion som ger inbyggt stöd för information om data som lagras i tabellen vid varje tidpunkt, istället för endast den aktuella datan.

Kom igång med systemversionsbaserade temporala tabelleroch granska användningsscenarier för tidstabeller.

Vad är en systemversionsbaserad temporal tabell?

En systemversionerad tidstabell är en typ av användartabell som är utformad för att hålla en fullständig historik över dataändringar, vilket möjliggör punkt-i-tid-analys. Denna typ av temporär tabell kallas en systemversionerad temporal tabell, eftersom systemet hanterar giltighetsperioden för varje rad (det vill säga Database Engine).

Varje temporal tabell har två explicit definierade kolumner, var och en med en datetime2 datatyp. Dessa kolonner kallas tidstypiska kolonner. Database Engine använder dessa periodkolumner uteslutande för att registrera giltighetsperioden för varje rad när en rad ändras. Huvudtabellen som lagrar aktuell data kallas den aktuella tabellen, eller den temporala tabellen.

Förutom dessa periodkolumner innehåller en temporal tabell även en referens till en annan tabell med ett speglat schema, som kallas historiktabell. Systemet använder historiktabellen för att automatiskt lagra den tidigare versionen av raden varje gång en rad i den tidsmässiga tabellen uppdateras eller tas bort. När den tidsmässiga tabellen skapas kan du ange en befintlig historiktabell (som måste vara schemakompatibel) eller låta systemet skapa en standardhistoriktabell.

Varför temporal?

Verkliga datakällor är dynamiska, och affärsbeslut bygger ofta på insikter som analytiker får genom datautveckling. Användningsfall för temporala tabeller är:

  • Granska alla dataändringar och utföra datatekniska uppgifter vid behov
  • Rekonstruera datatillståndet vid valfri tidpunkt i det förflutna
  • Beräkna trender över tid
  • Upprätthålla en långsamt föränderlig dimension för beslutsstödprogram
  • Återställa från oavsiktliga dataändringar och programfel

Hur fungerar tidsarbete?

Systemversionshantering för en tabell implementeras som ett par tabeller: en aktuell tabell och en historiktabell. Inom var och en av dessa tabeller definierar två extra datetime2-kolumner giltighetsperioden för varje rad:

  • periodstartkolumn: Systemet registrerar starttiden för raden i den här kolumnen, vanligtvis betecknad som ValidFrom kolumn.

  • periodslutkolumn: Systemet registrerar sluttiden för raden i den här kolumnen, vanligtvis betecknad som ValidTo kolumn.

Den aktuella tabellen innehåller det aktuella värdet för varje rad. Historiktabellen innehåller varje tidigare värde (den gamla versionen) för varje rad, om någon, samt starttid och sluttid för den period som den var giltig för.

diagram som visar hur en temporal tabell fungerar.

Följande skript illustrerar ett scenario med information om anställda:

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));

Mer information finns i Skapa en systemversionsbaserad temporal tabell.

  • Insättningar: Systemet sätter värdet för kolumnen ValidFrom till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemets klocka och tilldelar värdet för kolumnen ValidTo till maxvärdet av 9999-12-31. Då markeras raden som öppen.

  • Uppdateringar: Systemet lagrar det föregående värdet för raden i historiktabellen och sätter värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemets klocka. Detta markerar raden som stängd, med en period som har registrerats för vilken raden var giltig. I den aktuella tabellen uppdateras raden med sitt nya värde och systemet anger värdet för kolumnen ValidFrom till starttiden för transaktionen (i UTC-tidszonen) baserat på systemklockan. Värdet för den uppdaterade raden i den aktuella tabellen för kolumnen ValidTo förblir det maximala värdet för 9999-12-31.

  • Raderingar: Systemet lagrar det föregående värdet för raden i historiktabellen och sätter värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemets klocka. Detta markerar raden som stängd, med en period som registrerades för vilken den föregående raden var giltig. I den nuvarande tabellen tas raden bort. Frågor i den aktuella tabellen returnerar inte den här raden. Endast frågor som hanterar historikdata returnerar data som en rad stängs för.

  • Sammanslagning: Operationen beter sig exakt som om upp till tre satser (en INSERT, en UPDATE, och/eller en DELETE) utförs, beroende på vad som anges som handlingar i uttalandet MERGE .

De tider som registreras i systemet datetime2 kolumner baseras på själva transaktionens starttid. Till exempel har alla rader som infogats i en enda transaktion samma UTC-tid som registrerats i kolumnen som motsvarar början av SYSTEM_TIME perioden.

När du kör frågor om dataändringar i en tidstabell lägger databasmotorn till en rad i historiktabellen, även om inga kolumnvärden ändras.

Hur ställer jag frågor mot tidsdata?

SELECT ... FROM <table>-instruktionen har en ny sats FOR SYSTEM_TIME, med fem tidsspecifika underklausuler för att fråga efter data i de nuvarande och historiska tabellerna. Den här nya SELECT-instruktionssyntaxen stöds direkt i en enda tabell, sprids via flera kopplingar och via vyer ovanpå flera temporala tabeller.

När du använder klausulen FOR SYSTEM_TIME med en av de fem undersatserna i en fråga inkluderar resultaten historisk data från den temporala tabellen, som visas i bilden nedan.

Diagram som visar hur temporala frågor fungerar.

Följande fråga söker efter radversioner för en anställd med filtervillkoret WHERE EmployeeID = 1000 som var aktiva minst under en del av perioden mellan 1 januari 2021 och 1 januari 2022 (inklusive den övre gränsen):

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 filtrerar bort rader som har en giltighetsperiod med noll varaktighet (ValidFrom = ValidTo).

Database Engine genererar dessa rader om du utför flera uppdateringar på samma primärnyckel inom samma transaktion. I så fall returnerar temporär frågning endast radversioner före transaktionerna och aktuella rader efter transaktionerna.

Om du behöver inkludera dessa rader i analysen, fråga historiktabellen direkt.

I följande tabell representerar ValidFrom i kolumnen Kvalificerande rader värdet i kolumnen ValidFrom i tabellen som efterfrågas och ValidTo representerar värdet i kolumnen ValidTo i tabellen som efterfrågas. Fullständig syntax och exempel finns i FROM-sats plus JOIN, APPLY, PIVOToch Query-data i en systemversionsbaserad temporal tabell.

Expression Kvalificerande rader Anmärkning
AS OF date_time ValidFrom <= datum_tidAND ValidTo > datum_tiddatum_tid Returnerar en tabell med rader som innehåller de värden som var aktuella vid den angivna tidpunkten tidigare. Internt utförs en union mellan den temporala tabellen och dess historiktabell. Resultatet filtreras för att returnera värdena på raden som var giltig vid tidpunkten, som anges av parametern date_time. Värdet för en rad anses vara giltigt om värdet för system_start_time_column_name är mindre än eller lika med parametervärdet date_time och system_end_time_column_name-värdet är större än parametervärdet date_time.
FROM startdatum_tidTOslutdatum_tid ValidFrom < slutdatum_tidAND ValidTo >startdatum_tid Returnerar en tabell med värdena för alla radversioner som var aktiva inom det angivna tidsintervallet, oavsett om de började vara aktiva innan parametervärdet start_date_time för argumentet FROM eller om det upphörde att vara aktivt efter end_date_time parametervärdet för argumentet TO. Internt utförs en union mellan den temporala tabellen och dess historiktabell. Resultatet filtreras för att returnera värdena för alla radversioner som var aktiva när som helst under det angivna tidsintervallet. Rader som slutade vara aktiva exakt på den nedre gränsen som definierats av den FROM slutpunkten ingår inte, och poster som blev aktiva exakt på den övre gränsen som definierats av TO slutpunkten ingår inte heller.
BETWEEN startdatum_tidANDslutdatum_tid ValidFrom <= slutdatum_tidAND ValidTo >startdatum_tid Samma som tidigare i den FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time beskrivningen, förutom tabellen med rader som returneras innehåller rader som blev aktiva på den övre gränsen som definierats av end_date_time slutpunkten.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= startdatum_tidAND ValidTo <=slutdatum_tid Returnerar en tabell med värdena för alla radversioner som öppnades och stängdes inom det angivna tidsintervallet som definieras av de två periodvärdena för argumentet CONTAINED IN. Rader som blev aktiva exakt på den nedre gränsen eller upphörde att vara aktiva exakt på den övre gränsen inkluderas.
ALL Alla rader Returnerar unionen av rader som tillhör den aktuella tabellen och historiktabellen.

Dölj periodkolumnerna

Du kan dölja periodkolumnerna, så SELECT frågor som inte uttryckligen refererar till dem returnerar inte dessa kolumner, såsom SELECT * FROM <table>.

Om du vill returnera en dold kolumn måste du uttryckligen referera till den dolda kolumnen i frågan. På samma sätt fortsätter INSERT- och BULK INSERT-instruktioner som om dessa nya periodkolumner inte fanns (och kolumnvärdena fylls i automatiskt).

Mer information om hur du använder HIDDEN -satsen finns i CREATE TABLE och ALTER TABLE.

Samples

Relaterat innehåll

  • Last updated on