Systemversionierte temporale Tabelle erstellen

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL database in Microsoft Fabric

Sie können eine systemversionierte temporale Tabelle auf drei Arten erstellen, basierend darauf, wie Sie die Historiktabelle angeben:

  • Temporale Tabelle mit einer anonymen Verlaufstabelle: Sie geben das Schema der aktuellen Tabelle an und lassen das System eine entsprechende Verlaufstabelle mit einem automatisch generierten Namen erstellen.

  • Temporale Tabelle mit einer Standardverlaufstabelle: Sie geben den Namen des Verlaufstabellenschemas und der Tabelle an und lassen vom System eine Verlaufstabelle in diesem Schema erstellen.

  • Temporale Tabelle mit einer vorab erstellten, benutzerdefinierten Verlaufstabelle: Sie erstellen eine Verlaufstabelle, die Ihren Anforderungen am besten entspricht, und verweisen dann beim Erstellen der temporalen Tabelle auf diese Tabelle.

Erstellen einer temporalen Tabelle mit anonymer Verlaufstabelle

Das Erstellen einer temporalen Tabelle mit einer anonymen Verlaufstabelle ist eine praktische Möglichkeit für das schnelle Erstellen von Objekten insbesondere in Prototyp- und Testumgebungen. Es ist auch die einfachste Möglichkeit, eine temporale Tabelle zu erstellen, da sie keinen Parameter in der SYSTEM_VERSIONING Klausel benötigt. Das folgende Beispiel erstellt eine neue Tabelle bei aktivierter Systemversionierung, ohne den Namen der Verlaufstabelle zu definieren.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR (50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON
);

Bemerkungen

Eine systemversionierte temporale Tabelle muss einen definierten Primärschlüssel haben und genau eine PERIOD FOR SYSTEM_TIME, die mit zwei datetime2-Spalten definiert ist, die als GENERATED ALWAYS AS ROW START oder GENERATED ALWAYS AS ROW END deklariert sind.

Die PERIOD-Spalten werden immer als nicht nullfähig angenommen, auch wenn die Nullfähigkeit nicht angegeben ist. Wenn für die PERIOD-Spalten explizit angegeben ist, dass NULL-Werte zulässig sind, tritt bei der Anweisung CREATE TABLE ein Fehler auf.

Das Schema der Verlaufstabelle muss im Hinblick auf Spaltenanzahl, Spaltennamen, Sortierung und Datentypen stets an die aktuelle oder temporale Tabelle angepasst sein.

Die Datenbank-Engine erstellt automatisch eine anonyme Verlaufstabelle im selben Schema wie die aktuelle oder temporale Tabelle.

Der Name der anonymen Verlaufstabelle weist das folgende Format auf: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Das Suffix ist optional und wird nur hinzugefügt, wenn der erste Teil des Tabellennamens nicht eindeutig ist.

Die Verlaufstabelle wird als Rowstore-Tabelle erstellt. Sofern möglich, wird die PAGE-Komprimierung angewendet; andernfalls wird die Verlaufstabelle nicht komprimiert. Einige Tabellenkonfigurationen, z. B. SPARSE-Spalten, lassen beispielsweise keine Komprimierung zu.

Für die Verlaufstabelle wird ein standardmäßiger Clusterindex mit einem automatisch generierten Namen im Format IX_<history_table_name> erstellt. Der gruppierte Index enthält die PERIOD-Spalten (Ende, Anfang).

In der Fabric SQL-Datenbank wird die erstellte Verlaufstabelle nicht in Fabric OneLake gespiegelt.

Informationen zum Erstellen der aktuellen Tabelle als speicheroptimierte Tabelle finden Sie unter Temporale Tabellen mit Systemversionsverwaltung für speicheroptimierte Tabellen.

Erstellen einer temporären Tabelle mit Standardverlaufstabelle

Das Erstellen einer temporalen Tabelle mit einer Standardverlaufstabelle ist eine praktische Option, wenn Sie die Benennung steuern möchten und sich bei der Erstellung der Verlaufstabelle mit der Standardkonfiguration weiterhin auf das System verlassen möchten. Das folgende Beispiel erstellt eine neue Tabelle, bei der die Systemversionierung aktiviert ist und der Name der Verlaufstabelle explizit angegeben wird.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR (50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.DepartmentHistory
    )
);

Bemerkungen

Die Verlaufstabelle wird unter Anwendung der gleichen Regeln erstellt, die für das Erstellen einer „anonymen“ Verlaufstabelle gelten. Allerdings gelten die folgenden Regeln speziell für die benannte Verlaufstabelle.

  • Der Schemaname ist für den Parameter HISTORY_TABLE obligatorisch.

  • Wenn das angegebene Schema nicht vorhanden ist, tritt bei der Anweisung CREATE TABLE ein Fehler auf.

  • Wenn die im Parameter HISTORY_TABLE angegebene Tabelle bereits vorhanden ist, wird sie mit der neu erstellten temporalen Tabelle auf Schemakonsistenz und temporale Datenkonsistenz verglichen. Wenn Sie eine ungültige Verlaufstabelle angeben, tritt bei der Anweisung CREATE TABLE ein Fehler auf.

Erstellen einer temporären Tabelle mit benutzerdefinierter Verlaufstabelle

Das Erstellen einer temporalen Tabelle mit einer benutzerdefinierten Verlaufstabelle ist eine praktische Option, wenn Sie eine Verlaufstabelle mit spezifischen Speicheroptionen und verschiedenen auf historische Abfragen abgestimmten Indizes angeben möchten. Im folgenden Beispiel erstellen Sie eine benutzerdefinierte Verlaufstabelle mit einem Schema, das mit dem der temporalen Tabelle übereinstimmt. Diese Verlaufstabelle verfügt über einen gruppierten Columnstore-Index und einen zusätzlichen nicht gruppierten Rowstore-Index (B-Baum) für Punktabfragen. Nachdem du die Verlaufstabelle erstellt hast, erstellst du die Temporaltabelle und legst die benutzerdefinierte Verlaufstabelle als Standard-Verlaufstabelle fest.

Note

In der Dokumentation wird der Begriff „B-tree“ im Allgemeinen im Zusammenhang mit Indizes verwendet. In Rowstore-Indizes implementiert die Datenbank-Engine einen B+-Baum. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR (50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
    ON DepartmentHistory(ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR (50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.DepartmentHistory
    )
);

Bemerkungen

Wenn Sie vorhaben, analytische Abfragen auf historischen Daten durchzuführen, die Aggregate oder Fensterfunktionen verwenden, wird zur Zusammenpressung und Abfrageleistung dringend empfohlen, einen clustered columnstore als primären Index zu erstellen.

Wenn Sie temporale Tabellen für die Datenüberwachung verwenden möchten (d. h. wenn Sie Verlaufsänderungen für eine bestimmte Zeile in der aktuellen Tabelle suchen möchten), sollten Sie eine Rowstore-Verlaufstabelle mit einem gruppierten Index erstellen.

Die Verlaufstabelle kann keine Primärschlüssel, Fremdschlüssel, eindeutige Indizes, Tabelleneinschränkungen oder Trigger enthalten. Sie kann nicht zur Erfassung von Änderungsdaten, zur Änderungsnachverfolgung oder zur Transaktionsreplikation oder Mergereplikation konfiguriert werden.

Wenn Sie in der Fabric SQL-Datenbank und in Azure SQL-Datenbank die Fabric-Spiegelung konfiguriert haben, wird die vorhandene Tabelle nicht mehr gespiegelt, wenn Sie eine vorhandene Tabelle während der zeitlichen Tabellenerstellung als Verlaufstabelle verwenden.

Eine nicht temporale Tabelle in eine systemversionierte temporale Tabelle ändern

Sie können Systemversionierung auf einer bestehenden nicht-temporalen Tabelle aktivieren, zum Beispiel wenn Sie eine benutzerdefinierte temporale Lösung auf integrierte Unterstützung migrieren möchten.

Angenommen, Sie verfügen über eine Gruppe von Tabellen, bei denen die Versionsverwaltung mit Triggern implementiert ist. Die Verwendung temporärer Systemversionsverwaltung ist weniger komplex und bietet zusätzliche Vorteile, z. B.:

  • Unveränderliche Geschichte
  • Neue Syntax für „Zeitreiseabfragen“
  • Eine bessere DML-Leistung
  • Minimale Wartungskosten

Beim Konvertieren einer bestehenden Tabelle sollten Sie die HIDDEN Klausel verwenden, um die neuen PERIOD Spalten (die datetime2-SpaltenValidFrom und ValidTo) auszublenden, um bestehende Anwendungen zu vermeiden, die keine expliziten Spaltennamen angeben (zum Beispiel SELECT * oder INSERT ohne Spaltenliste) und nicht für neue Spalten ausgelegt sind.

Versionierung zu nicht-temporären Tabellen hinzufügen

Wenn Sie das Nachverfolgen von Änderungen für eine nicht temporale Tabelle mit den Daten starten möchten, müssen Sie die PERIOD-Definition hinzufügen und optional einen Namen für die leere Verlaufstabelle angeben, die SQL Server für Sie erstellt:

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.9999999'),
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (
        SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = History.InsurancePolicy
        )
    );
GO

Important

Die Genauigkeit für DATETIME2 muss mit der Genauigkeit für die zugrunde liegende Tabelle übereinstimmen.

Bemerkungen

Das Hinzufügen von Spalten, die keine NULL-Werte zulassen, mit Standardwerten zu einer vorhandenen Tabelle mit Daten ist in allen Editionen außer SQL Server Enterprise Edition ein Datengrößenvorgang (in SQL Server Enterprise Edition wäre es ein Metadatenvorgang). Bei einer vorhandenen großen Verlaufstabelle mit Daten in SQL Server Standard Edition kann das Hinzufügen einer Nicht-NULL-Spalte ein aufwendiger Vorgang sein.

Einschränkungen für Spalten für den Periodenanfang und das Periodenende müssen sorgfältig gewählt werden:

  • Der Standardwert für die Startspalte gibt an, ab welchem Zeitpunkt vorhandene Zeilen als gültig betrachtet werden. Es kann nicht als ein Datums-/Uhrzeitpunkt in der Zukunft angegeben werden.

  • Die Endzeit muss als maximaler Wert für eine bestimmte datetime2-Genauigkeit angegeben werden, z. B. 9999-12-31 23:59:59 oder 9999-12-31 23:59:59.9999999.

Das Hinzufügen PERIOD führt eine Datenkonsistenzprüfung an der aktuellen Tabelle durch, um sicherzustellen, dass die vorhandenen Werte für Periodenspalten gültig sind.

Wenn bei der Aktivierung von SYSTEM_VERSIONING eine vorhandene Verlaufstabelle angegeben wird, erfolgt eine Datenkonsistenzprüfung der aktuellen Tabelle und der Verlaufstabelle. Diese Prüfung kann übersprungen werden, indem Sie DATA_CONSISTENCY_CHECK = OFF als zusätzlichen Parameter angeben.

Vorhandene Tabellen zur integrierten Unterstützung migrieren

Dieses Beispiel zeigt, wie eine vorhandene Lösung basierend auf Triggern zu integrierter temporärer Unterstützung migriert wird. Dieses Beispiel geht davon aus, dass die aktuelle benutzerdefinierte Lösung die aktuellen und historischen Daten in zwei separate Benutzertabellen (ProjectTaskCurrent und ProjectTaskHistory) aufteilt.

Wenn Ihre bestehende Lösung eine einzige Tabelle verwendet, um tatsächliche und historische Zeilen zu speichern, sollten Sie die Daten vor den im folgenden Beispiel gezeigten Migrationsschritten in zwei Tabellen aufteilen. Löschen Sie zuerst den Trigger aus der zukünftigen temporalen Tabelle. Stellen Sie dann sicher, dass die PERIOD-Spalten keine NULL-Werte zulassen.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
    ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
    ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
    ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
    ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
    ADD PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent
    SET (
        SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.ProjectTaskHistory,
            DATA_CONSISTENCY_CHECK = ON
        )
    );

Bemerkungen

Durch das Verweisen auf vorhandene Spalten in der PERIOD-Definition wird für diese Spalten implizit generated_always_type zu AS_ROW_START und AS_ROW_END geändert.

Das Hinzufügen PERIOD führt eine Datenkonsistenzprüfung an der aktuellen Tabelle durch, um sicherzustellen, dass die vorhandenen Werte für Periodenspalten gültig sind.

Wir empfehlen Ihnen dringend, SYSTEM_VERSIONING mit DATA_CONSISTENCY_CHECK = ON festzulegen, um Datenkonsistenzprüfungen für die vorhandenen Daten zu erzwingen.

Wenn versteckte Spalten bevorzugt werden, verwenden Sie folgenden Befehl:

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Verwandte Inhalte

  • Last updated on