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
select * 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