Einleitung
Vielen Leuten stellt sich dieselbe Frage: SQL Server und Temporale Tabellen, was ist das eigentlich und wie benutze ich es? Im BI Umfeld steht man häufig vor der Anforderung importierte Daten aus Quellsystemen zu versionieren. Dies ist häufig mit erheblichem Programmieraufwand verbunden. Beispielsweise werden Trigger oder Versionierungsprozeduren eingesetzt, um die Änderungen an den Daten zu versionieren.
Seit dem SQL Server 2016 bietet das neue Feature Temporale Tabellen (Anmerkung: Temporal für zeitlich und nicht für Temporär) mit Versionsverwaltung (engl. system-versioned temporal tables) die Möglichkeit ohne großen Programmieraufwand eine Versionierung der Daten vorzunehmen. Hierdurch erhält man einen lückenlosen Verlauf aller Datenänderungen.
Beispiel zum Thema
Wir erstellen eine temporale Tabelle Produkte. Die erste Besonderheit liegt darin, dass wir u.a. zwei Spalten gueltig_von and gueltig_bis vom Datentyp datetime2 definieren. Diese sogenannten Zeitraumspalten werden ausschließlich vom System verwendet, um die Gültigkeitszeiträume aufzuzeichnen.
Des Weiteren beinhaltet jede temporale Tabelle einen Verweis auf eine weitere Tabelle mit identischem Schema, hier: ProdukteHistorie. Die Änderung oder Löschung einer Zeile bewirkt, dass diese in die (Verlaufs-)Tabelle gespeichert und zeitlich abgegrenzt wird. In der temporalen Tabelle Produkte stehen dagegen nur die aktuell gültigen Zeilen.
Die Erstellung der temporalen Tabelle Produkte mit dem Verweis auf die Tabelle ProdukteHistorie (wird automatisch erstellt) funktioniert wie folgt:
Wird nun Eintrag am 10.08.2017 um 10:00 Uhr vorgenommen, so wird eine gültige Zeile in die temporale Tabelle eingefügt. In den Spalten gueltig_von und gueltig_bis wird automatisch der Gültigkeitszeitraum eingetragen.
Wie man sieht, enthält die Tabelle ProduktHistorie bisher keine Einträge. Ändern wir nun beispielsweise am 11.08.2017 um 12:00 Uhr bei diesem Produkt das Nettogewicht_in_Gramm von 500 nach 450, so steht in der Tabelle Produkte der aktuelle Zustand und in der Tabelle ProdukteHistorie wird die Vorversion eingetragen. D.h. die bisher gültige Zeile wird zeitlich abgegrenzt.
Nun ändern wir noch den Preis am 01.09.2017 um 09:00 Uhr:
Hierdurch wird die bisher gültige Version abgegrenzt und in der Verlaufstabelle ProdukteHistorie gespeichert. Die Tabelle Produkte enthält (wie immer) die aktuelle Version.
Abfrage-Statements
Es gibt verschiedene Möglichkeiten die unterschiedlichen Versionen abzufragen.
Durch das Hinzufügen der Klausel „FOR SYSTEM_TIME ALL“ zu dem Select-Statement erhält man alle Versionen:
Möchte man alle Zeilen, die in einem gewissen Zeitraum gültig waren, abfragen, so fügt man die Klausel „FOR SYSTEM_TIME BETWEEN <StartDateTime> AND <EndDatetime>“ ins Select-Statement ein:
Um Zeilen gewisser Zeiträume abzufragen existieren 4 Klauseln, die sich in der Behandlung der Zeitraumgrenzen unterscheiden:
- FOR SYSTEM_TIME BETWEEN AND
- FOR SYSTEM_TIME FROM TO
- FOR SYSTEM_TIME CONTAINED IN ( , )
- FOR SYSTEM_TIME AS OF
Fazit
Mit dem neuen Feature ist die Versionierung von Tabellen, bzw. Datenzeilen ohne großen Aufwand möglich. Somit erhält man eine lückenlose Änderungshistorie ohne Trigger oder ähnliche Workarounds programmieren zu müssen. Sehr praktisch ist außerdem die Tatsache, dass Änderungen an der Tabelle automatisch an die Verlaufstabelle weitergereicht werden. Wird beispielsweise eine Spalte „Hersteller“ der Tabelle Produkte hinzugefügt (ALTER TABLE dbo.Produkte ADD Hersteller INT NULL), so wird diese Spalte automatisch auch der Verlaufstabelle hinzugefügt. Änderungen an der neuen Spalte werden genauso, wie bei den ursprünglichen Spalten versioniert. Dies war in der Vergangenheit immer mit Programmieraufwand verbunden. Bei weiteren Fragen, zögern Sie nicht uns zu kontaktieren.
