Temporale Tabellen

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed Instance

Temporale Tabellen (auch als „temporale Tabellen mit Versionsverwaltung durch das System“ bezeichnet) sind ein Datenbankfeature, das integrierte Unterstützung für das Bereitstellen von Informationen über die zu jedem Zeitpunkt in der Tabelle gespeicherten Daten mit sich bringt, anstatt nur die aktuell in einer Tabelle gespeicherten Daten zu unterstützen.

Sie können die ersten Schritte mit temporalen Tabellen mit Versionsverwaltung durch das System durchführen und sich mit Verwendungsszenarios für temporale Tabellen vertraut machen.

Was ist eine temporale Tabelle mit Versionsverwaltung durch das System?

Eine temporale Tabelle mit Versionsverwaltung durch das System ist ein Benutzertabellentyp, der darauf ausgelegt ist, den Verlauf aller Datenänderungen lückenlos zu speichern und einfache zeitpunktbezogene Analysen zu ermöglichen. Bei diesem Typ temporaler Tabelle ist die Rede von einer Versionsverwaltung durch das System, da die Gültigkeitsdauer für jede Zeile vom System (d. h. von der Datenbank-Engine) verwaltet wird.

Jede temporale Tabelle weist zwei explizit definierte Spalten auf, beide vom Datentyp datetime2 . Diese Spalten werden als Zeitraumspalten bezeichnet. Diese Zeitraumspalten werden bei jeder Änderung einer Zeile ausschließlich vom System zum Aufzeichnen des Gültigkeitszeitraums verwendet. Die Haupttabelle, in der aktuelle Daten gespeichert werden, wird als aktuelle Tabelle oder ganz einfach als temporale Tabelle bezeichnet.

Über diese Zeitraumspalten hinaus enthält eine temporale Tabelle außerdem einen Verweis auf eine weitere Tabelle (Verlaufstabelle) mit einem gespiegelten Schema. Das System verwendet diese Verlaufstabelle, um bei jeder Aktualisierung oder Löschung einer Zeile in der temporalen Tabelle automatisch die Vorversion der Zeile zu speichern. Während der Erstellung von temporalen Tabellen können Benutzer eine vorhandene Verlaufstabelle (deren Schema kompatibel sein muss) angeben oder vom System eine standardmäßige Verlaufstabelle erstellen lassen.

Warum temporal?

Real verwendete Datenquellen sind dynamisch, und in der Mehrzahl der Fälle beruhen Geschäftsentscheidungen auf Erkenntnissen, die Analysten aus der Entwicklung der Daten ableiten. Zu den Einsatzgebieten von temporalen Tabellen zählen beispielsweise:

  • Die Überwachung aller Datenänderungen und ggf. die Ausführung von Datenforensik
  • Wiederherstellung des Status der Daten zu beliebigen Zeitpunkten in der Vergangenheit
  • Berechnen von Trends im zeitlichen Verlauf
  • Warten einer sich langsam verändernden Dimension für Anwendungen zur Entscheidungsunterstützung
  • Wiederherstellung nach unbeabsichtigten Datenänderungen und Anwendungsfehlern

Wie funktioniert temporal?

Die Versionsverwaltung durch das System für eine Tabelle wird in Form eines Tabellenpaars implementiert, von denen eine die aktuelle und die andere eine Verlaufstabelle ist. Innerhalb jeder dieser Tabellen werden die zwei zusätzlichen datetime2-Spalten verwendet, um den Gültigkeitszeitraum für jede Zeile zu definieren:

  • Spalte des Systemzeitraumstarts: Das System zeichnet die Anfangszeit für die Zeile in dieser Spalte auf, normalerweise als ValidFrom-Spalte bezeichnet.
  • Spalte des Systemzeitraumendes: Das System zeichnet die Endzeit für die Zeile in dieser Spalte auf, normalerweise als ValidTo-Spalte bezeichnet.

Die aktuelle Tabelle enthält den aktuellen Wert für jede Zeile. Die Verlaufstabelle enthält jeden früheren Wert (die alte Version) für jede Zeile, falls vorhanden, sowie die Anfangszeit und Endzeit für den Zeitraum, für den er gültig war.

Diagram showing how a temporal table works.

Das folgende Skript veranschaulicht ein Szenario mit Mitarbeiterinformationen:

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

Weitere Informationen finden Sie unter Erstellen einer temporalen Tabelle mit Versionsverwaltung durch das System.

  • Einfügungen: Das System legt den Wert für die ValidFrom Spalte basierend auf der Systemzeit auf den Anfangszeitpunkt der aktuellen Transaktion (in der UTC-Zeitzone) fest und weist der ValidTo Spalte den Maximalwert von 9999-12-31 als Wert zu. Dadurch wird die Zeile als offen gekennzeichnet.
  • Updates: Das System speichert den vorhergehenden Wert des Datensatzes in der Verlaufstabelle und legt den Wert für die ValidTo Spalte basierend auf der Systemzeit auf den Anfangszeitpunkt der aktuellen Transaktion (in der UTC-Zeitzone) fest. Dadurch wird die Zeile als geschlossen gekennzeichnet. Zudem wird der Zeitraum vermerkt, zu dem die Zeile gültig war. In der aktuellen Tabelle wird die Zeile mit dem neuen Wert aktualisiert, und das System legt den Wert für die ValidFrom-Spalte basierend auf der Systemzeit auf den Anfangszeitpunkt der Transaktion (in der UTC-Zeitzone) fest. Der Wert für die aktualisierte Zeile in der aktuellen Tabelle für die ValidTo Spalte verbleibt auf dem Maximalwert von 9999-12-31.
  • Löschungen: Das System speichert den vorhergehenden Wert der Zeile in der Verlaufstabelle und legt den Wert für die ValidTo Spalte basierend auf der Systemzeit auf die Startzeit der aktuellen Transaktion (in der UTC-Zeitzone) fest. Dadurch wird die Zeile als geschlossen gekennzeichnet. Zudem wird der Zeitraum vermerkt, zu dem die vorhergehende Zeile gültig war. In der aktuellen Tabelle wird die Zeile entfernt. Bei Abfragen der aktuellen Tabelle wird die Zeile nicht zurückgegeben. Nur bei Abfragen von Verlaufsdaten werden Daten zurückgegeben, für die eine Zeile geschlossen ist.
  • Zusammenfügen: Der Vorgang verläuft so, als würden bis zu drei Anweisungen ausgeführt (ein INSERT, ein UPDATE, und/oder ein DELETE), je nachdem, welche Aktionen in der MERGE Anweisung angegeben sind.

Wichtig

Die in den datetime2-Spalten des Systems aufgezeichneten Zeiten basieren auf dem Anfangszeitpunkt der Transaktion selbst. Beispielsweise wird für alle Zeilen, die innerhalb einer einzelnen Transaktion eingefügt werden, in der Spalte, die dem Beginn des SYSTEM_TIME Zeitraums entspricht, die gleiche UTC-Zeit aufgezeichnet.

Hinweis

Wenn Sie Datenänderungsabfragen in einer temporalen Tabelle ausführen, fügt die Datenbank-Engine der Verlaufstabelle eine Zeile hinzu, auch wenn sich keine Spaltenwerte ändern.

Wie lassen sich temporale Daten abfragen?

Die SELECT ... FROM <table> Anweisung enthält eine neue Klausel FOR SYSTEM_TIME mit fünf zeitabhängigen Unterklauseln zur Abfrage von Daten in den aktuellen und historischen Tabellen. Die Syntax dieser neuen SELECT Anweisung wird für Einzeltabellen direkt unterstützt, durch mehrfache Joins weitergegeben und für zusammenfassende Sichten mehrerer temporaler Tabellen unterstützt.

Wenn Sie die FOR SYSTEM_TIME Klausel mit einer der fünf Unterklauseln abfragen, werden wie in der folgenden Abbildung dargestellt Verlaufsdaten aus der temporalen Tabelle einbezogen.

Diagram showing how Temporal Querying works.

Die folgende Abfrage sucht nach Zeilenversionen für einen Mitarbeiter mit der Filterbedingung WHERE EmployeeID = 1000, der mindestens eine Zeit lang zwischen dem 1. Januar 2021 und dem 1. Januar 2022 (einschließlich der oberen Grenze) aktiv war:

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;

Hinweis

FOR SYSTEM_TIME filtert Zeilen mit einer Gültigkeitsdauer von 0 (ValidFrom = ValidTo).

Diese Zeilen werden generiert, wenn mehrere Updates für den gleichen Primärschlüssel innerhalb der gleichen Transaktion ausgeführt werden. In diesem Fall gibt die temporale Abfrage nur Zeilenversionen vor den Transaktionen sowie aktuelle Zeilen nach den Transaktionen zurück.

Wenn diese Zeilen in die Analyse mit aufgenommen werden müssen, fragen Sie die Verlaufstabelle direkt ab.

In der Tabelle unten steht ValidFrom in der Spalte „Qualifizierte Zeilen“ für den Wert in der ValidFrom Spalte in der abgefragten Tabelle, und ValidTo stellt den Wert in der Spalte ValidTo in der abgefragten Tabelle dar. Die vollständige Syntax und Beispiele finden Sie unter FROM (Transact-SQL) und Abfragen von Daten in einer temporalen Tabelle mit Versionsverwaltung durch das System.

Ausdruck Qualifizierte Zeilen Hinweis
AS OFdate_time ValidFrom <=date_timeAND ValidTo >date_time Gibt eine Tabelle mit Zeilen zurück, die die Werte enthalten, die zum angegebenen Zeitpunkt in der Vergangenheit aktuell waren. Intern wird eine Union zwischen der temporalen Tabelle und ihrer Verlaufstabelle ausgeführt, und die Ergebnisse werden so gefiltert, dass die Werte in der Zeile zurückgegeben werden, die zu dem durch den Parameter date_time angegebenen Zeitpunkt gültig waren. Der Wert für eine Zeile ist gültig, wenn der Wert system_start_time_column_name kleiner als oder gleich dem Parameterwert date_time und der Wert system_end_time_column_name größer als der Parameterwert date_time ist.
FROMstart_date_timeTOend_date_time ValidFrom <end_date_timeAND ValidTo >start_date_time Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des angegebenen Zeitbereichs aktiv waren, unabhängig davon, ob ihre Aktivität vor dem start_date_time Parameterwert für das FROM Argument begonnen hat oder ihre Aktivität nach dem end_date_time-Parameterwert für das TO Argument geendet hat. Intern wird eine Vereinigung zwischen der temporalen Tabelle und der zugehörigen Verlaufstabelle durchgeführt. Die Ergebnisse werden so gefiltert, dass die Werte für alle Zeilenversionen zurückgegeben werden, die zu irgendeinem Zeitpunkt innerhalb des angegebenen Zeitbereichs aktiv waren. Zeilen, die genau an dem durch den FROM Endpunkt definierten unteren Grenzwert beendet wurden, sind ebenso wenig enthalten wie Datensätze, die genau an dem durch den TO Endpunkt definierten oberen Grenzwert aktiviert wurden.
BETWEENstart_date_timeANDend_date_time ValidFrom <=end_date_timeAND ValidTo >start_date_time Gleich wie oben in der Beschreibung zu FOR SYSTEM_TIME FROMstart_date_timeTOend_date_time , mit dem Unterschied, dass sie Zeilen enthält, die an dem durch den end_date_time Endpunkt definierten oberen Grenzwert aktiv wurden.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >=start_date_timeAND ValidTo <=end_date_time Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des von den zwei Zeitraumwerte für das CONTAINED IN Argument definierten Zeitbereichs geöffnet und geschlossen wurden. Zeilen, die genau beim unteren Grenzwert aktiv wurden, oder deren Aktivität genau beim oberen Grenzwert endete, sind enthalten.
ALL Alle Zeilen Gibt die Vereinigungsmenge der Zeilen zurück, die der aktuellen und der Verlaufstabelle angehören.

Ausblenden der Zeitraumspalten

Sie können die Zeitraumspalten ausblenden, sodass Abfragen, die nicht explizit darauf verweisen, diese Spalten nicht zurückgeben (z. B. beim Ausführen von SELECT * FROM <table>).

Um eine ausgeblendete Spalte zurückzugeben, müssen Sie in der Abfrage explizit auf die ausgeblendete Spalte verweisen. Analog dazu verhalten sich INSERT and BULK INSERT Anweisungen, als ob diese neuen Zeitraumspalten nicht vorhanden wären (und die Spaltenwerte werden automatisch aufgefüllt).

Weitere Informationen zur Verwendung der HIDDEN Klausel finden Sie unter CREATE TABLE und ALTER TABLE.

Beispiele

ASP.NET

In diesem ASP.NET Core Webanwendung erfahren Sie, wie Sie eine temporale Anwendung mit temporalen Tabellen erstellen.

Herunterladen der AdventureWorks-Beispieldatenbank

Sie können die AdventureWorks-Datenbank für SQL Server herunterladen, die Features für temporale Tabellen enthält.

Zugehöriger Inhalt