Microsoft SQL Server
Zeittabellen

Bemerkungen

Zeittabellen erstellen

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

INSERTS: Bei einem INSERT setzt das System den Wert für die ValidFrom- Spalte auf die Anfangszeit der aktuellen Transaktion (in der UTC-Zeitzone) basierend auf der Systemuhr und weist den Wert für die ValidTo- Spalte dem Maximalwert von 9999- zu. 12-31. Dies kennzeichnet die Reihe als offen.

UPDATES: Bei einem UPDATE speichert das System den vorherigen Wert der Zeile in der Protokolltabelle und setzt den Wert für die Spalte ValidTo auf den Anfangszeitpunkt der aktuellen Transaktion (in der UTC-Zeitzone) basierend auf der Systemuhr. Dies kennzeichnet die Zeile als geschlossen, wobei ein Zeitraum aufgezeichnet wird, für den die Zeile gültig war. In der aktuellen Tabelle wird die Zeile mit ihrem neuen Wert aktualisiert, und das System setzt den Wert für die Spalte ValidFrom auf die Anfangszeit der Transaktion (in der UTC-Zeitzone) basierend auf der Systemuhr. Der Wert für die aktualisierte Zeile in der aktuellen Tabelle für die ValidTo- Spalte bleibt der Maximalwert von 9999-12-31.

DELETES : Bei einem DELETE speichert das System den vorherigen Wert der Zeile in der Protokolltabelle und setzt den Wert für die Spalte ValidTo auf den Anfangszeitpunkt der aktuellen Transaktion (in der UTC-Zeitzone) basierend auf der Systemuhr. Dies kennzeichnet die Zeile als geschlossen, wobei ein Zeitraum aufgezeichnet wird, für den die vorherige Zeile gültig war. In der aktuellen Tabelle wird die Zeile entfernt. Abfragen der aktuellen Tabelle geben diese Zeile nicht zurück. Nur Abfragen, die sich auf Verlaufsdaten beziehen, geben Daten zurück, für die eine Zeile geschlossen ist.

MERGE : Bei einer MERGE verhält sich die Operation genau so, als würden bis zu drei Anweisungen ( INSERT , UPDATE und / oder DELETE ) ausgeführt, je nachdem, was als Aktionen in der MERGE- Anweisung angegeben ist.

Tipp: Die in den Systemdatetime2-Spalten aufgezeichneten Zeiten basieren auf der Anfangszeit der Transaktion. Beispielsweise wird für alle Zeilen, die in eine einzige Transaktion eingefügt werden, dieselbe UTC-Zeit in der Spalte aufgezeichnet, die dem Beginn des SYSTEM_TIME- Zeitraums entspricht.

Wie frage ich zeitliche Daten ab?

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Gibt den angegebenen Zeitpunkt zurück (FOR SYSTEM_TIME AS of) )

Gibt eine Tabelle mit Zeilen zurück, die die Werte enthalten, die zum angegebenen Zeitpunkt in der Vergangenheit aktuell waren (aktuell).

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

FÜR SYSTEM_TIME ZWISCHEN UND

Das Gleiche wie oben in der Beschreibung von FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time> beschrieben, mit der Ausnahme, dass die zurückgegebene Tabelle mit Zeilen Zeilen enthält, die an der vom Endpoint <end_date_time> definierten oberen Grenze aktiv wurden.

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

FÜR SYSTEM_TIME FROM ZU

Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des angegebenen Zeitbereichs aktiv waren, unabhängig davon, ob sie vor dem Parameterwert <start_date_time> für das Argument FROM aktiv waren oder nach dem Parameterwert <end_date_time> für den Parameter nicht mehr aktiv waren Zu argumentieren. Intern wird eine Vereinigung zwischen der temporalen Tabelle und ihrer Verlaufstabelle durchgeführt, und die Ergebnisse werden gefiltert, um die Werte für alle Zeilenversionen zurückzugeben, die zu einem beliebigen Zeitpunkt im angegebenen Zeitraum aktiv waren. Zeilen, die genau an der vom FROM-Endpunkt definierten unteren Grenze aktiv wurden, werden eingeschlossen, und Datensätze, die genau an der vom TO-Endpunkt definierten oberen Grenze aktiv wurden, sind nicht enthalten.

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

FÜR SYSTEM_TIME ENTHALTEN ( . )

Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des angegebenen Zeitraums geöffnet und geschlossen wurden, der durch die beiden datetime-Werte für das Argument CONTAINED IN definiert wird. Zeilen, die genau an der unteren Grenze aktiv wurden oder nicht mehr genau an der oberen Grenze aktiv waren, werden eingeschlossen.

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

FOR SYSTEM_TIME ALL

Gibt die Vereinigung von Zeilen zurück, die zur aktuellen und zur Verlaufstabelle gehören.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Erstellen einer speicheroptimierten Systemversion der temporalen Tabelle und Bereinigen der SQL Server-Verlaufstabelle

Das Erstellen einer temporalen Tabelle mit einer Standardprotokolltabelle ist eine bequeme Option, wenn Sie die Benennung steuern möchten und dennoch das System zur Erstellung der Protokolltabelle mit der Standardkonfiguration benötigen. In dem folgenden Beispiel ist eine neue systemversionierte, speicheroptimierte temporale Tabelle mit einer neuen datenträgerbasierten Verlaufstabelle verknüpft.

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

Bereinigen der SQL Server-Verlaufstabelle Mit der Zeit kann die Verlaufstabelle erheblich anwachsen. Da das Einfügen, Aktualisieren oder Löschen von Daten aus der Verlaufstabelle nicht zulässig ist, können Sie die Verlaufstabelle nur bereinigen, indem Sie die Versionierung des Systems deaktivieren:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); GEHEN

Löschen Sie nicht benötigte Daten aus der Verlaufstabelle:

    DELETE FROM dbo.EmployeeHistory

WO Endzeit <= '2017-01-26 14:00:29';

und aktivieren Sie die Systemversion erneut:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo]. [EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));

Das Bereinigen der Protokolltabelle in Azure SQL-Datenbanken unterscheidet sich ein wenig, da Azure SQL-Datenbanken eine integrierte Unterstützung für das Bereinigen der Protokolltabelle bieten. Erstens muss die Bereinigung der temporären Verlaufsbeibehaltung auf Datenbankebene aktiviert werden:

ALTER DATABASE CURRENT

SET TEMPORAL_HISTORY_RETENTION ON GO

Legen Sie dann die Aufbewahrungszeit pro Tabelle fest:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 TAGE));

Dadurch werden alle Daten in der Verlaufstabelle gelöscht, die älter als 90 Tage sind. Die lokalen Datenbanken von SQL Server 2016 unterstützen TEMPORAL_HISTORY_RETENTION und HISTORY_RETENTION_PERIOD nicht. Eine der beiden obigen Abfragen wird in den lokalen Datenbanken von SQL Server 2016 ausgeführt.

Für TEMPORAL_HISTORY_RETENTION wird ein Fehler angezeigt:

Msg 102, Level 15, State 6, Line 34

Falsche Syntax in der Nähe von 'TEMPORAL_HISTORY_RETENTION'.

Für HISTORY_RETENTION_PERIOD wird ein Fehler auftreten:

Msg 102, Level 15, State 1, Line 39

Falsche Syntax in der Nähe von 'HISTORY_RETENTION_PERIOD'.