Dela via

Temporala tabeller

  • Artikel

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 kallade systemversionsbaserade temporala tabeller) är en databasfunktion som ger inbyggt stöd för att tillhandahålla information om data som lagras i tabellen när som helst, snarare än bara de data som är korrekta vid den aktuella tidpunkten.

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

Vad är en systemversionsbaserad temporal tabell?

En systemversionsbaserad temporal tabell är en typ av användartabell som är utformad för att behålla en fullständig historik över dataändringar, vilket gör det enkelt att analysera tidpunkter. Den här typen av tidstabell kallas för en systemversionsbaserad temporal tabell, eftersom systemet hanterar giltighetsperioden för varje rad (d.s. databasmotorn).

Varje temporal tabell har två explicit definierade kolumner, var och en med en datetime2 datatyp. Dessa kolumner kallas period kolumner. Dessa periodkolumner används uteslutande av systemet för att registrera giltighetsperioden för varje rad, när en rad ändras. Huvudtabellen som lagrar aktuella data kallas för den aktuella tabellen, eller helt enkelt som 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 oftast är affärsbeslut beroende av insikter som analytiker kan få från datautvecklingen. Användningsfall för temporala tabeller är:

  • Granska alla dataändringar och utföra datatekniska uppgifter vid behov
  • Återskapa datans tillstånd från vilken tidpunkt som helst 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. I var och en av dessa tabeller används två extra datetime2 kolumner för att definiera 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.

  • Infogar: Systemet anger värdet för kolumnen ValidFrom till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan och tilldelar värdet för kolumnen ValidTo till det maximala värdet för 9999-12-31. Då markeras raden som öppen.

  • Uppdateringar: Systemet lagrar föregående värde för raden i historiktabellen och anger värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan. 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.

  • Borttagningar: Systemet lagrar det tidigare värdet för raden i historiktabellen och anger värdet för kolumnen ValidTo till starttiden för den aktuella transaktionen (i UTC-tidszonen) baserat på systemklockan. 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.

  • Merge: Åtgärden fungerar exakt som om upp till tre instruktioner (en INSERT, en UPDATEoch/eller en DELETE) körs, beroende på vad som anges som åtgärder i MERGE-instruktionen.

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 gör en fråga med hjälp av FOR SYSTEM_TIME-satsen med någon av de fem underklausulerna inkluderas historiska data från den temporala tabellen, som visas i följande bild.

diagram som visar hur temporal frågekörning 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).

Dessa rader genereras 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.

Uttryck Kvalificerande rader Anteckning
AS OF date_time ValidFrom <= date_timeAND ValidTo >date_time 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 start_date_timeTOend_date_time ValidFrom < end_date_timeAND ValidTo >start_date_time 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 start_date_timeANDend_date_time ValidFrom <= end_date_timeAND ValidTo >start_date_time 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 >= start_date_timeAND ValidTo <=end_date_time 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 välja att dölja periodkolumnerna, så att frågor som inte uttryckligen refererar till dem inte returnerar dessa kolumner (till exempel när du kör 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.

Prover

Relaterat innehåll