Datenbank-Zeitreisen: Temporal Tables im SQL Server

Wer hat den Preis geändert, und wie hoch war er eigentlich vor drei Wochen? Wann ist dieser Datensatz gelöscht worden, und wie sah er kurz davor aus? Sobald eine Datenbank produktiv läuft, tauchen solche Fragen auf – und oft steht man mit leeren Händen da, weil die alten Werte längst überschrieben sind. Der klassische Ausweg ist eine selbstgebaute Historientabelle samt Triggern, die bei jedem INSERT, UPDATE und DELETE die alte Zeile wegschreiben. Das funktioniert, ist aber Handarbeit. Der SQL Server bringt seit der Version 2016 eine eingebaute Lösung mit: System-Versioned Temporal Tables. Die Datenbank führt die komplette Änderungshistorie vollautomatisch mit, und Abfragen zu einem beliebigen Zeitpunkt der Vergangenheit sind eine Zeile Zusatz-SQL. Wie das gelingt, wie und wo die alten Daten landen und wie Du eine bestehende Tabelle nachträglich versionierst, liest Du in diesem Beitrag – und auch, was Temporal Tables nicht können.

Was eine Temporal Table eigentlich ist

Eine systemversionierte temporale Tabelle besteht immer aus einem Paar: der aktuellen Tabelle, die – wie gewohnt – den jeweils gültigen Stand jeder Zeile enthält, und einer zugehörigen Historientabelle, in der jede frühere Version einer Zeile aufbewahrt wird. Beide zusammen ergeben eine lückenlose Zeitleiste.

Damit der SQL Server weiß, ab wann und bis wann eine bestimmte Version einer Zeile gültig war, braucht die Tabelle zwei zusätzliche Spalten vom Typ DATETIME2, die sogenannten Periodenspalten. Die eine hält den Startzeitpunkt (im Beispiel gleich GueltigVon), die andere den Endzeitpunkt (GueltigBis).

Solange eine Zeile aktuell ist, steht in GueltigBis der Maximalwert 9999-12-31 23:59:59.9999999 – der SQL Server liest das als “gilt bis auf Weiteres”.

Ändern wir die Zeile, passiert im Hintergrund Folgendes: Die bisherige Version wandert mit ihrem echten Endzeitpunkt in die Historientabelle, und in der aktuellen Tabelle steht die neue Version mit frischem Startzeitpunkt. Wir müssen dafür nichts programmieren – kein Trigger, keine Prozedur.

Ein Punkt vorweg, der später noch wichtig wird: Der SQL Server speichert diese Zeitstempel in UTC, nicht in lokaler Zeit.

Für uns in Deutschland heißt das ein bis zwei Stunden Versatz zur Uhr an der Wand. Darauf kommen wir bei den Abfragen zurück.

Was Temporal Tables nicht können

Temporal Tables erstellen nur eine Kopie eines geänderten oder gelöschten Datensatzes und tragen ein, von wann bis wann dieser Datensatz gültig war. Wir können damit nicht automatisch eine Information hinterlegen, wer die neue Version des Datensatzes angelegt hat.

Es wird für eine historisierte Version eines Datensatze auch nicht eingetragen, ob dieser Datensatz durch einen geänderten Datensatz ersetzt oder ob dieser gelöscht wurde – es wird lediglich jede geänderte oder gelöschte Version eines Datensatzes gespeichert.

Die Beispieldatenbank anlegen

Bauen wir zunächst eine ganz normale Tabelle ohne jede Versionierung auf und füllen sie mit ein paar Datensätzen – so, wie eine bestehende Anwendung sie mitbringen könnte.

Erst im nächsten Schritt rüsten wir die Historie nach. Das entspricht dem häufigsten Fall aus der Praxis: Die Tabelle ist längst da, die Historie soll dazu.

Das erledigt Listing 1. Es legt die Datenbank TemporalDemo an, darin die Tabelle tblProdukte mit den Feldern ProduktID, Bezeichnung und Preis – und füllt drei Zeilen ein.

CREATE DATABASE TemporalDemo;
GO
USE TemporalDemo;
GO
CREATE TABLE dbo.tblProdukte
(
    ProduktID   INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Produkt PRIMARY KEY CLUSTERED,
    Bezeichnung NVARCHAR(100) NOT NULL,
    Preis       DECIMAL(10,2) NOT NULL
);
GO
INSERT INTO dbo.tblProdukte (Bezeichnung, Preis)
VALUES (N'Kaffeebohnen Brasil',   12.90),
       (N'Teekanne Gusseisen',    34.50),
       (N'Espressotassen-Set',    19.99);
GO

Listing 1: Beispieldatenbank und Tabelle tblProdukte anlegen und mit Daten füllen

Ein Detail ist keine Kür, sondern Voraussetzung für alles Weitere: Die Tabelle tblProdukte hat einen Primärschlüssel (PK_Produkt auf ProduktID). Ohne Primärschlüssel lässt sich später keine Systemversionierung einschalten – der SQL Server braucht ihn, um eine Zeile über ihre Versionen hinweg eindeutig zu verketten. Wer hier eine Tabelle ohne Schlüssel hat, muss also zuerst einen nachrüsten.

Versionierung an einer bestehenden Tabelle nachrüsten

Jetzt kommt der eigentliche Schritt: Wir erweitern die bestehende Tabelle tblProdukte um die beiden Periodenspalten und schalten die Systemversionierung ein.

Das geschieht in Listing 2 in zwei getrennten ALTER TABLE-Anweisungen – erst die Spalten und die Periode, dann die Versionierung selbst.

USE TemporalDemo;
GO
ALTER TABLE dbo.tblProdukte ADD
    GueltigVon DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL
        CONSTRAINT DF_Produkt_GueltigVon DEFAULT SYSUTCDATETIME(),
    GueltigBis DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL
        CONSTRAINT DF_Produkt_GueltigBis
        DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (GueltigVon, GueltigBis);
GO
ALTER TABLE dbo.tblProdukte
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tblProdukteHistory));
GO

Listing 2: Die bestehende Tabelle tblProdukte um Periodenspalten erweitern und die Systemversionierung einschalten

Das ist zugleich das allgemeine Rezept für jede Tabelle, die es in Deiner Datenbank schon gibt – einzige harte Voraussetzung bleibt der Primärschlüssel aus dem vorigen Abschnitt.

Gehen wir das durch, denn hier lauern gleich mehrere Stolpersteine. Die beiden neuen Spalten sind mit GENERATED ALWAYS AS ROW START bzw. ROW END gekennzeichnet – das sagt dem SQL Server, dass er diese Werte setzt und wir sie niemals von Hand befüllen.

Genau daraus ergibt sich das erste Problem: Die Spalten sind NOT NULL, aber unsere Tabelle enthält bereits Daten. Woher soll der SQL Server die Zeitstempel für die drei vorhandenen Zeilen nehmen? Wir würden die Werte nun gern selbst eintragen – aber das ist bei GENERATED ALWAYS-Spalten nicht möglich.

Die Lösung sind die beiden DEFAULT-Constraints: Sie liefern die Startwerte für die schon vorhandenen Zeilen. Ohne diese Defaults bricht das ALTER TABLE bei einer gefüllten Tabelle mit einer Fehlermeldung ab.

Der zweite Stolperstein steckt in der Wahl dieser Defaults. Für GueltigVon nehmen wir SYSUTCDATETIME(), also den aktuellen Zeitpunkt in UTC. Für GueltigBis muss es zwingend der Maximalwert 9999-12-31 23:59:59.9999999 sein – nicht etwa ebenfalls die aktuelle Zeit.

Der Endzeitpunkt markiert bei einer aktuellen Zeile das “gilt bis auf Weiteres”, und er muss größer sein als der Startzeitpunkt. Wer hier versehentlich auch SYSUTCDATETIME() einsetzt, erntet einen Fehler.

Und der dritte Punkt ist weniger ein Fehler als eine ehrliche Einordnung: Die Historie beginnt in dem Moment, in dem wir die Versionierung einschalten. Unsere drei Bestandszeilen bekommen alle denselben Startzeitpunkt – den der Umstellung -, nicht ihr echtes Anlagedatum. Rückwirkend entsteht keine Historie, die vorher nie aufgezeichnet wurde. Das ist keine Schwäche des Verfahrens, aber gut zu wissen, bevor man sich über gleiche Zeitstempel wundert.

Die zweite Anweisung schaltet mit SET (SYSTEM_VERSIONING = ON …) die Versionierung scharf und legt dabei die Historientabelle dbo.tblProdukteHistory an. Den Namen geben wir bewusst selbst an. Lässt man ihn weg, erzeugt der SQL Server eine automatisch benannte Tabelle nach dem Muster MSSQL_TemporalHistoryFor_… samt Objekt-ID – funktioniert, ist aber unhandlich.

Im Objekt-Explorer des SQL Server Management Studios erkennst Du das Ergebnis sofort: Die Tabelle tblProdukte trägt jetzt ein kleines Uhr-Symbol, und darunter hängt als Unterknoten die zugehörige tblProdukteHistory (siehe Bild 1). Im Kontextmenü sehen wir auch gleich, dass wir die Datensätze dieser Tabelle nicht bearbeiten können – es fehlt der Eintrag Oberste 200 Einträge bearbeiten.

Bild 1: Der Objekt-Explorer zeigt die versionierte Tabelle Produkt mit Uhr-Symbol und der eingerückten Historientabelle ProduktHistory

Das war der Weg für eine Tabelle, die es schon gibt – der häufigste Fall in der Praxis. Steht die Tabelle dagegen noch gar nicht, gibt es einen kürzeren Weg, den wir uns weiter unten im Abschnitt zum direkten Anlegen ansehen.

Wo die alten Daten landen

Werfen wir einen Blick auf die Verteilung, bevor überhaupt etwas geändert wurde. Die aktuelle Tabelle tblProdukte enthält unsere drei Zeilen, die Historientabelle tblProdukteHistory ist noch leer – logisch, denn es gibt ja noch keine früheren Versionen. Erst eine Änderung füllt die Historie.

Wichtig ist die Rollenverteilung: In der aktuellen Tabelle steht immer nur der jeweils gültige Stand. Jede überschriebene oder gelöschte Version zieht in die Historientabelle um.

Diese ist eine ganz normale Tabelle, die Du direkt abfragen kannst – nur ändern darfst Du sie nicht, solange die Versionierung eingeschaltet ist. Der SQL Server verwaltet ihren Inhalt allein.

Und noch einmal der Hinweis auf die Zeitzone, weil er gleich praktisch wird: Die Werte in GueltigVon und GueltigBis stehen in UTC. Ein Datensatz, der um 14:00 Uhr deutscher Sommerzeit geändert wird, trägt in der Historie den Zeitstempel 12:00 Uhr.

Daten verändern und die Historie beobachten

Damit die Historie etwas zu zeigen hat, spielen wir ein kleines Szenario durch: Der Kaffee wird teurer, die Teekanne kommt in den Ausverkauf, und das Espressotassen-Set fliegt aus dem Sortiment.