SQL-Daten historisieren – Beispiel mit Temporal Tables und Azure SQL Database

Eine häufige Anforderung – die manchmal voher feststeht, manchmal jedoch erst nachträglich formuliert wird – ist die Protokollierung oder Historisierung einer oder mehreren Entitäten. Das kann beispielsweise der Fall sein, wenn in einem System / App nachvollzogen werden muss, wie sich eine Entität – Nutzerdaten, Objektdaten etc. – über die Zeit ändert – sei es um Analysen durchführen zu können oder aus Audit-Gründen.

In diesem Artikel fügen wir Historisierungs-Funktionalität für eine vorhandene Tabelle hinzu. Diese Funktion ist sowohl On-Premises als auch in Azure SQL verfügbar. Wir wollen z.B. erreichen, dass die Pflege von Produktdaten historisiert wird – der Vertrieb möchte nachvollziehen, wie sich Produktänderungen auf Verkaufszahlen auswirken.

Wir werden in diesem Artikel das Konzept von temporalen Datenbanken. Diese Datenbanken stellen Fakten dar, die zu verschiedenen Zeitpunkten in der Gegenwart, Vergangenheit und Zukunft geltung haben. Konventionelle Datenbanken sind aktuelle Datenbanken (current databases), stellen also nur Fakten dar, die jetzt in der Gegenwart Gültigkeit haben. Diese Problematik wurde im Jahre 1992 von Richard Snodgrass addressiert, der den Anstoß dazu gab, dass die temporale Datenbank Community einige SQL-Erweiterungen entwickelt. Somit ist in 194 die TSQL2-Spezifikation entstanden, das über mehreren Umwege und mit einigen Änderungen ins ISO/IEC 9075-7-Standard eingebettet wurde. Das Konzept der system-versioned Tabellen wird dort beschrieben. Das

Setup

Benötigten Tools: SQL Server Management Studio (SSMS)

Bei SSMS habe ich mich für die in Oktober 2025 aktuellste Version 21 entschieden. Es können aber auch andere Clients verwendet werden.

In Azure habe ich eine SQL Database (PaaS) für dieses Beispiel mit Development-Workload und einen SQL Server mit Microsoft Entra-only Authentication erstellt.

Schritte

• Mithilfe einer SQL-Abfrage (z.B. mithilfe von SQL Server Management Studio) lässt sich die vorhandene Tabelle anpassen. Die Spalten ValidFrom und ValidTo werden hinzugefügt

ALTER TABLE SalesLT.Product
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE SalesLT.Product 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SalesLT.ProductHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductHistory
ON [SalesLT].[ProductHistory]
WITH (DROP_EXISTING = ON);

Nach den ersten 2. SQL-Abschnitten ist die Tabelle praktisch als temporal table markiert (auch die Tabellenicon verändert sich) und hat zwei neue Spalten bekommen.

Wenn Du folgende Fehlermeldung bekommst, dann ist die Azure SQL Datenbank nicht in der richtigen Service Tier [siehe auch Link 1]. Mindestens S3 ist erforderlich für Columnstore support. Folgendes schreibt Kevin Farles (principal program manager bei Microsoft) im Azure Blog:

Columnstore indexes are designed to be extremely efficient for queries which do scans and aggregations across millions and billions of rows of data. They are fundamentally different structures, which physically group data by column, rather than by row.

Azure Blog (Link 1)

Bedeutet: Ein Columnstore Index gruppiert Daten physisch nach Spalten, nicht wie üblich nach Zeilen. Das ist bei einer zeitbasierten Spalte eindeutig von Vorteil da wir in diesem Fall in der Abfrage mehrere Zeilen anhand von Zeitspannen bestimmen.

Optional: Upgrade Database Tier

Um das Problem oben zu beheben muss die Preisstufe gegebenenfalls erhöht werden. Hierfür kann z.B. über den Azure Portal in der Datenbankeinstellungen das Pricing Tier anpassen. Im Bild erkennt man, dass die Datenbank auf Basic (S0) konfiguriert war – was für übliche Dev-Szenarien ausreicht.

Durch Klick auf „Basic“ gelangt man ins Compute + Storage screen. Hier muss Standard als Service tier ausgewählt werden und mindestens S3 (>= 100 DTU)

Historisierung in Aktion – Wert ändern

Als Beispiel nehme ich eine Änderung vor und verändere den Wert in der Spalte „Size“ von 58 auf 99.

Abfragen historischer Werte

Um z.B. ältere Versionen einer bestimmten Zeile zu erhalten, kann man folgende Abfrage nutzen (ich nutze hier die ID 680, die ich oben verändert habe):

DECLARE @hourAgo datetime2 = DATEADD(HOUR, -1, SYSUTCDATETIME());
SELECT * FROM SalesLT.Product FOR SYSTEM_TIME AS OF @hourAgo
WHERE ProductID = 680
ORDER BY ProductID DESC

Den Zeitraum kann man beliebig erweitern, am nützlichsten ist die Analyse unter Verwendung von Zeitspannen – Beispielsweise wollen wir Produktänderungen vom gestrigen Tag abfragen:

DECLARE @twoDaysAgo datetime2 = DATEADD(DAY, -2, SYSUTCDATETIME());
DECLARE @aDayAgo datetime2 = DATEADD(DAY, -1, SYSUTCDATETIME());
SELECT * FROM SalesLT.Product FOR SYSTEM_TIME BETWEEN @twoDaysAgo AND @aDayAgo
ORDER BY ProductID DESC

Zusammenfassung

Hier habe ich ein Minimalbeispiel für die Aktivierung und Nutzung der Temporal Tables-Funktion in Azure SQL. Für die Datenanalyse basierend auf historischen Werte mit nur minimaler Schemaänderung ideal und performant. Weitere Tipps wie z.B. die Konfiguration von Speicherbegrenzungen für historische Daten und weitere Abfragebeispiele empfehle ich den Microsoft-Artikel (Link 3) worauf ich mein hier vorgestelltes Beispiel basiert.