Schritt für Schritt: SQL Server 2016 – temporal tables


Andreas Rauch

Nein, bitte nicht verwechseln: temporal tables haben nichts zu tun mit temporary tables table variables und dergleichen. Im Gegenteil, die Daten  einer temporal table bleiben bestehen – auch nach Neustart. Also was sollte denn dann der Zweck sein? Kurz und einfach gesagt, stellen temporal tables den historischen Verlauf von Datensätze dar. In SQL auch "System versioning" genannt.

Das Prinzip dahinter ist schnell erklärt: Tabellen, für die ein Verlauf der Datensätze organisiert werden soll, benötigen zwingend 2 Spalten vom Typ datetime2, die den Gültigkeitszeitraum eines Datensatzes markieren. Zu diesen Tabellen wird außerdem eine weitere Tabellen benötigt, die über dasselbe Tabellenschema verfügt und den historischen Verlauf der Datensätze speichert.

Weder die Tabelle mit den historischen Werten, noch die Funktion, welche eine Änderung der Daten abfängt und die "alten" Datensätze in die Verlaufstabelle schreibt müssen tatsächlich selbst geschrieben bzw. angelegt werden.

Wollen wir mal einen Blick darauf werfen..

System_Versioning aktivieren

Das Aktivieren des SYSTEM_VERSIONING geschieht als Erweiterung des CREATE TABLE Statement.

Create table contacts 
(
Cid int identity primary key,
Lastname varchar(50),
Firstname varchar(50),
Birthday date,
Phone varchar(50),
email varchar(50),
ValidFrom datetime2 Generated always as row start not null,
ValidTo datetime2 Generated always as row end not null,
Period for system_time (Startdatum, Enddatum)
)

with (system_Versioning = ON (History_table=dbo.Contactshistory))
  

Essentiell sind natürlich die Zeile, die den Gültigkeitsbereich definieren. ValidFrom gibt dabei den Zeitpunkt an, seit wann dieser Datensatz in der Form existiert und ValidTo bis wann er gültig ist. Im Falle der Originaltabelle wird das datum auf das Jahr 9999 gesetzt, was für die meisten einen gewissen beruhigenden Charakter darstellt. Die Spaltennamen dürfen sind frei wählbar. Die Option GENERATED ALWAYS kann auch mit dem Merkmal HIDDEN verwendet werden. Das ist dann erforderlich, wenn Sie die beiden Spalten für den Gültigkeitsbereich standardmäßig immer versteckt halten wollen. Zumindest thereotisch.. in der aktuellen CTP2 wurde das wohln noch nicht implementiert und quittiert daher mit einem Syntaxfehler.

ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN not null,
ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN not null,


PERIOD gibt die zu verwendenden datetime2 Spalten für den Gültigkeitsbereich an

Period for system_time (ValidFrom, ValidTo)

..und folgende Zeile definiert die Versionstabelle. Diese kann bereits vorher erstellt worden sein. Falls nicht wird Sie in dem moment erstellt. Gibt man gar keine Versionstabelle so weist der Name folgendes Muster aus: MSSQL_TemporalHistoryFor_<objectid>

with (system_Versioning = ON (History_table=dbo.Contactshistory))

Und so sieht das Ergebnis im SSMS aus:


Natürlich kann auch nachträglich eine Tabelle als SYSTEM_VERSIONING initialisiert werden.


Aktivierung des SYSTEM_VERSIONING bei bestehenden Tabellen

Die Initialisierung von temporal tables bei bestehenden Tabellen vollzieht sich ebenfalls rel. Einfach und ist mit obiger synteax auch recht gut naschvollziehbar:

Fall 1: Eine Tabelle hat bereits, warum auch immer, zwei Spalten die für den Gültigkeitsbereich stehen:

CREATE
TABLE Demo2

(

SP1 int identity primary key,
SP2 int,
StartFrom datetime2 not null, EndTo datetime2 not null

);

--Aktivierung der PERIOD

ALTER
TABLE demo1
ADD PERIOD FOR SYSTEM_TIME(StartFrom,EndTo)

--Aktivierung des SYSTEM_VERSIONING

ALTER
TABLE demo1
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.demohistory, DATA_CONSISTENCY_CHECK = ON))
 

Fall 2: Die Spalten für das SYSTEM_VERSIONING sind noch nicht vorhanden..

--Alternativ, wenn die Start und End-Zeitstempelspalten noch nicht vorhanden sind

CREATE
TABLE Demo3
(
SP1 int identity primary key, SP2 int
)
 

ALTER
TABLE demo3
ADD PERIOD FOR SYSTEM_TIME (StartFrom, EndTo),
StartFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETUTCDATE(),
EndTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL DEFAULT CONVERT(DATETIME2,'9999.12.31');
     

ALTER
TABLE demo3
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.demo3history, DATA_CONSISTENCY_CHECK = ON))
 

Arbeiten mit temporal tables

Nun wollen wir mal mit dem SYSTEM_VERSIONING arbeiten. Zuerst ein paar Datensätze zum Spielen

insert
into Contacts
(Lastname,Firstname,Birthday, Phone, email)

select 'Kent', 'Clark','3.4.2010', '089-3303003', 'clarkk@krypton.universe'

insert
into Contacts
(Lastname,Firstname,Birthday, Phone, email)

select 'Wayne', 'Bruce','3.4.2012', '08677-3303003', 'brucew@gotham.city'

Und nun die Änderungen, die zu einer Versionierung der Datensätze führt

update contacts set email = 'wer@earth.de' where cid = 1
update contacts set Phone = 'w3434' where cid = 1
update contacts set Lastname = 'Wayne' where cid = 1

--etwas später

update contacts set email = 'asas@earth.de' where cid = 1
update contacts set Phone = 'w34sasaa34' where cid = 2
update contacts set Lastname = 'Smith' where cid = 1

Das Ergebnis:

select * from contacts
s
elect * from ContactsHistory


In der Originaltabelle findet man logischer Weise den aktuell gültigen Datensatz wieder. Die Zeitstempelspalten geben den Bereich an, seit wann der Datensatz gütlig ist. In den Verlaufstabellen dagegen, befindet jeder geänderte Datensatz – ebenfalls mit den Zeitstempelspalten für seine Lebensdauer.

Versionen finden

Das Praktische wollen wir natürlich nicht außer Acht lassen: Die Suche nach einer bestimmten Version.

Entweder man frägt die temporal table direkt ab:

--Abfrage auf Versiossverlauf

select
*
from contactshistory

where

    Startdatum >= '2015-06-28 09:17:04'

    and

    Enddatum <= '2015-06-28 09:17:50'


Das ist natürlich dann empfehlenswert, wenn man den Verlauf eines Datensatzes sehen möchte.

Sofern nur die letzte gütlige Version zu einem Zeitpunkt gesucht wird, kann man das auch direkt von der Originaltabelle erfahren.

--Coole Abfrage: direkt auf Originatabelle

select
*
from contacts

    FOR SYSTEM_TIME AS OF '2015-06-27 15:49:11.1448833'
    where cid =1


Nachträgliches Ändern von SYSTEM_VERSIONING Tabellen

Haben wir was vergessen? Ach ja: kann man versionierte Tabellen auch nachträglich ändern? Klar! System_Versioning ausschalten, Änderungen auf beiden Tabellen ausfürhren, SYSTEM_VERSIONING wieder einschalten. Guggste..

--SYSTEM_VERSIONING deaktiveren

Alter
Table contacts set (system_versioning= off)

--Beliebige Spalten auf beiden Seiten hinzufügen

Alter
Table contacts add Fax varchar(50)

Alter
Table contactshistory add Fax varchar(50)

--und wieder einschalten

ALTER TABLE contacts

SET (system_versioning=on (History_table=dbo.Contactshistory))

Deaktivieren des SYSTEM_VERSIONING bzw Löschen der Verlaufstabelle


Und was, wenn ich die Versionierung nicht mehr haben will. Dann SYSTEM_VERSIONING ausschalten und Tabellen jeweils löschen.

alter
table Contacts Set (system_versioning=OFF)

GO

drop
table contacts

drop
table Contactshistory

Randnotizen

Abgesehen Davon, dass ich teporal tables extrem einfach in der Anwendung finde, bleiben noch ein paar Details zu erwähnen übrig.

  • Temporal tables sind per default zeilenkomprimiert
  • Die Originaltabllen müssen einen Primary key haben
  • Löscht man den Clustered IX der temporal Tabelle, dann kann man auch Clustered Columnstore IX verwenden –absolutly perfect! 12 Punkte!!
  • Geht nicht bei Filetables
  • Kein truncate table bei SYSTEM_VERSIONING = ON
  • Kein instead of Trigger
  • Support für AlwaysOn

Coole Sache..

Demnächst mehr mit Schritt für Schritt: SQL 2016

Kommentare sind geschlossen