Temporal Tables – SQL Server 2016 CTP 3.1

Mit dem SQL Server 2016 kommt eine sehr gelungene neue Funktion – Temporal Tables.

Am letzten Donnerstag konnte ich im Rahmen eines Webinar von Microsoft „SQL Server 2016 – Mission Critical Database“ einen ersten Einblick in Temporal Tables gewinnen und wie der Zufall es wollte, ging es am Abend dann für mich weiter mit dem SQLPASS RG Treffen Hamburg, hier hielt Sascha Lorenz ebenfalls einen Vortrag über Temporal Tables.

Irgendwie war das Thema äußerst spannend, so dass ich beschloss mich näher damit zu beschäftigen…

Microsoft schreibt in seinem Technet-Blog zu Temporal Tables
Temporal beschreibt die Möglichkeiten, wie man Daten und Analysen umgeht, welche sich über die Zeit ändern, dazu gehören:

• Unterstützung für Computed-Columns
• Unterstützung von einer oder zwei Periodenspalten und der Kennzeichnung HIDDEN, so dass die Möglichkeit besteht eine reibungslose Migration für bestehende Anwendungen durchzuführen. Folgendes gilt für die HIDDEN Periodenspalten:
  • Spalte wird nicht in SELECT * Statement ausgegeben
  • INSERT-Anweisung ohne Spaltenliste erwarten keine Einträge/Werte für ausgeblendeten Spalten
  • Versteckte Spalten müssen explizit in allen Abfragen, welche einen direkten zeitlichen Bezug zur Referenz-Tabelle oder anderen Objekten haben oder auf die Temporal Tables verweisen, enthalten. (zum Beispiel Views)
  • BULK INSERT-Skripte, welche vor der Aktivierung von Temporal Tables (und dem Einsatz von Hidden Periodenspalten) einwandfrei gearbeitet haben, werden auch weiterhin arbeiten, da die ausgeblendeten Spalten automatisch bestückt werden.
  • is_hidden Flag in sys.columns View auf 1 gesetzt wurde

Aber was sind denn nun Temporal Tables???
Wenn man sich mal umhört (Sascha hat dies mal gemacht), dann erhält man die merkwürdigsten Aussagen, weil sich noch keiner damit befasst hat oder weil keiner es verstanden hat.

Temporal Tables sind nicht zu verwechseln mit Temporary Tables, welche nur temporär also kurzzeitig in der Datenbank verfügbar sind, obwohl beide einen Bezug zum Zeitlichen (temporal) haben. Bei den Temporal Tables bezieht sich der zeitliche Faktor allerdings auf die Aktualität bzw zeitliche Gültigkeit von Daten. Vereinfacht ausgedrückt könnte man von Historientabellen sprechen, früher musste man extra Funktionen und Skripte programmieren um historische Daten extra zu speichern oder gar seine Applikation entsprechend anpassen, um ggf auf historische Daten zuzugreifen. Mit Temporal Tables wird dies um einiges einfacher und transparenter.

Temporal Tables sind im Grunde nichts Neues, allerdings im SQL Server halt doch… Temporal Tables sind schon lange über den allgemein gültigen Standard ANSI SQL 2011 definiert und bekannt.

Ich möchte hier in die Nutzung einführen und auf die ein oder andere Ecke hinweisen, die sich im Betrieb auffällig benehmen könnte.

Um Temporal Tables nutzen zu können, muss man zuerst ein paar Dinge beachten, denn man kann nicht einfach nur das Feature aktivieren:

 WITH (SYSTEM_VERSIONING = ON)

Nein, dazu gehört leider etwas mehr… zum Beispiel

• es muss ein Primary Key vorhanden sein, sonst würden die einzelnen Einträge keine Abhängigkeit zueinander finden
• muss man der betreffenden Tabelle zwei neue Spalten schenken, beide können mit dem HIDDEN Merkmal versteckt werden
  • Start Time (Datetime2) => wann begann dieser Wert aktuell zu sein
  • End Time (Datetime2)  => wann wurde der Datensatz geändert und war damit nicht mehr aktuell

Beispiel: Erstellen eines Temporal Tables

In diesem Beispiel wird eine Tabelle inklusive der oben genannten SYSTEM_TIME-Spalten angelegt und über die WITH-Option „System_Versioning“ die Versionierung bzw Historisierung aktiviert.

Zusätzlich zu SYSTEM_VERSIONING = ON kann man auch noch einen weiteren Parameter angeben, nämlich den Tabellenname der Versionierungstabelle. Solange kein eigener Tabellenname angegeben wird, vergibt der SQL Server selber einen, in meinem Fall : MSSQL_TemporalHistoryFor_885578193
Um dies zu verhindern sollte man einen eigenen Tabellennamen angeben, so wird auch später die Fehlersuche einfacher.

WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory))

Um die Funktion zu verdeutlichen und besser erläutern zu können, brauchen wir Beispieldaten… 😉

Nach diesen Inserts ist die Historien-Tabelle natürlich noch leer. Nun werden wir einfach mal zwei Abteilungen tauschen, um zu demonstrieren, was passiert. Also führen wir zwei Updates durch…

UPDATE [dbo].[Department] SET [DeptName] = 'NEW_HumanResources' WHERE [ParentDeptID] = 2;
UPDATE [dbo].[Department] SET [DeptName] = 'NEW_Finance' WHERE [ParentDeptID] = 4;

Nun finden wir in der Historientabelle zwei neue Einträge:

Kommen zu den Features bei der Anlage, mit denen wir großartige Programmierungsarbeiten in unserer Applikation „vermeiden“ können.
Man kann die beiden zusätzlichen Spalten (Start Time, End Time (DATETIME2)) mit einem HIDDEN Flag versehen, damit sie in Abfragen „nicht“ auftauchen.
Ich habe das nicht bewusst in Gänsefüßchen gesetzt, die Spalten sind natürlich in den Objects-Systemtabellen sichtbar, auch wenn man sich ein Create- oder Drop-/Create-Statement ausgeben läßt oder die beiden Spalten im Select explizit mit angibt, aber für *-Statements verschwinden die Spalten aus dem Ergebnis.

Zusatz: Natürlich sollte man in Applikationen nur in sehr begrenzten Situationen zu einer *-Abfrage greifen, wenn man sich daran hält, dann hat man später (oder jetzt) keinen weiteren Programmieraufwand.

Löschen von Temporal Tables

Das Löschen von eben diesen Tabellen ist nicht ganz so einfach und könnte so manchen DBA zur Verzweiflung bringen…
Wenn man sich einmal das Kontext-Menu der versionierten Tabelle anschaut, stellt man fest, dass es keine Delete-Option mehr gibt.
Natürlich kann man beide Tabellen mittels T-SQL löschen, ich möchte hier aber auf die Details eingehen.

Um nun eine versionierte Tabelle zu löschen, muss man erst die Verknüpfung beider Tabellen (Original und Historie) lösen, hierzu verwenden wir ein Alter-Statement:

ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )

Durch dieses Lösen der Verbindung „entstehen“ zwei einzelne Tabellen, welche keinerlei Verbindung untereinander haben, nun könn(t)en beide Tabellen wieder über das Kontext-Menü oder einfache Drop-Statements gelöscht werden.

Nutzt man das Kontext-Menü für ein Drop-Table kommen wir zu folgendem Ergebnis, welches das Vorgehen verdeutlicht.

USE [Temporal_Table]
GO
ALTER TABLE [dbo].[Department] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Department]
GO
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_885578193]
GO

Achtung Stolperfalle !!!
Wenn man jetzt aber einfach mittels OFF/ON die Versionierung wieder abschalten bzw einschalten möchte, wird nicht automatisch die alte History-Tabelle verwendet! Man muss beim Reaktivierung nun explizit den Tabellenname wieder angeben. Habe ich einmal die Tabellen-Verknüpfung getrennt, kennen sich beide Tabellen nicht mehr und finden nicht automatisch wieder zu einander!

Weitere Möglichkeiten der Temporal Tables:

Man kann – bevor man die Versionierung (wieder) aktiviert – die Historien-Tabelle selber erstellen, vorab befüllen falls man diese Daten im Rahmen einer Migration vielleicht schon hat. Ebenso kann man die Tabelle mit einer normalen Partitionierung versehen, hierzu muss ich erst noch ein paar Daten sammeln, um eine entsprechende Dokumentation erstellen zu können => also später mehr dazu.
Denn dieses Thema klang auch sehr spannend, ob man nun zum Beispiel nach Geschäftsjahren partitioniert und dann nach einer gewissen Anzahl von Jahren „einfach“ einzelne Partitionen abtrennen kann, um wieder Platz zu schaffen oder Daten zu archivieren.
Es gibt in Sachen „Temporal Tables“ in Zukunft sicherlich noch einen oder mehrere Beiträge… ich bleibe auf jeden Fall am Ball.