Freigeben über

Erstellen einer temporären Tabelle mit Systemversionsverwaltung

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure SQL Managed Instance

Es gibt drei Methoden zum Erstellen einer temporalen Tabelle mit Systemversionsverwaltung, die sich darin unterscheiden, wie die Verlaufstabelle angegeben wird:

  • Temporale Tabelle mit einer anonymen Verlaufstabelle: Sie geben das Schema der aktuellen Tabelle an und lassen eine entsprechende Verlaufstabelle mit einem automatisch generierten Namen vom System 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 keine Parameter in der SYSTEM_VERSIONING-Klausel erforderlich sind. Im folgenden Beispiel wird eine neue Tabelle mit aktivierter Systemversionsverwaltung erstellt, 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);

Hinweise

Für eine temporale Tabelle mit Systemversionsverwaltung muss ein Primärschlüssel und genau eine PERIOD FOR SYSTEM_TIME mit zwei datetime2-Spalten definiert sein, die als GENERATED ALWAYS AS ROW START oder GENERATED ALWAYS AS ROW END deklariert sind.

Die PERIOD-Spalten dürfen keine NULL-Werte zulassen, auch wenn keine Angabe zur NULL-Zulässigkeit gemacht wurde. 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.

Eine anonyme Verlaufstabelle wird automatisch im gleichen Schema wie die aktuelle oder temporale Tabelle erstellt.

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 Rowstoretabelle 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 gruppierter Standardindex mit einem automatisch generierten Namen im Format IX_<history_table_name> erstellt. Der gruppierte Index enthält die PERIOD-Spalten (Ende, Anfang).

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 standardmäßigen Verlaufstabelle ist eine praktische Möglichkeit, wenn Sie die Benennung steuern möchten, die Verlaufstabelle aber trotzdem mit der Standardkonfiguration vom System erstellt werden soll. Im folgenden Beispiel wird eine neue Tabelle mit aktivierter Systemversionsverwaltung erstellt, wobei der Name der Verlaufstabelle explizit definiert 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));

Hinweise

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 Möglichkeit, wenn Benutzer*innen eine Verlaufstabelle mit bestimmten Speicheroptionen und zusätzlichen Indizes festlegen möchten. Im folgenden Beispiel wird eine benutzerdefinierte Verlaufstabelle mit einem Schema erstellt, das auf die zu erstellende temporale Tabelle abgestimmt ist. Für diese benutzerdefinierte Verlaufstabelle werden ein gruppierter Columnstore-Index und ein weiterer nicht gruppierter Rowstore-Index (B-Struktur) für Punktsuchen erstellt. Nach Erstellung der benutzerdefinierten Verlaufstabelle wird die temporale Tabelle unter Angabe der benutzerdefinierten Verlaufstabelle als Standardverlaufstabelle erstellt.

Hinweis

In der SQL Server-Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert SQL Server eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. 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));

Hinweise

Wenn Sie analytische Abfragen für die historischen Daten ausführen möchten, die Aggregate oder Fensterfunktionen einsetzen, ist es zum Zwecke der Leistung bei Komprimierung und Abfrage sehr zu empfehlen, einen gruppierten 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.

Ändern einer nicht temporalen Tabelle in eine temporale Tabelle mit Systemversionsverwaltung

Sie können die Systemversionsverwaltung für eine vorhandene Tabelle aktivieren, z. B., wenn Sie eine benutzerdefinierte temporale Lösung zu integrierter 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änderlichen Verlauf
  • Neue Syntax für „Zeitreiseabfragen“
  • Eine bessere DML-Leistung
  • Minimale Wartungskosten

Ziehen Sie beim Konvertieren einer vorhandenen Tabelle die Verwendung der Klausel HIDDEN in Betracht, um die neuen PERIOD-Spalten (die datetime2-Spalten ValidFrom und ValidTo) auszublenden und so Auswirkungen auf vorhandene Anwendungen zu vermeiden, die Spaltennamen nicht explizit angeben (z.B. SELECT * oder INSERT ohne Spaltenliste) und keine neuen Spalten verarbeiten können.

Hinzufügen der Versionsverwaltung zu nicht temporären Tabellen

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

Wichtig

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

Hinweise

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 des Zeitraumstarts und -endes 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 kein Zeitpunkt in der Zukunft als datetime-Wert 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.

Durch das Hinzufügen von PERIOD wird für die aktuelle Tabelle eine Datenkonsistenzprüfung durchgeführt, um sicherzustellen, dass die vorhandenen Werte für Zeitraumspalten 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.

Migrieren von vorhandenen Tabellen zu integrierter Unterstützung

Dieses Beispiel zeigt, wie eine vorhandene Lösung basierend auf Triggern zu integrierter temporärer Unterstützung migriert wird. In diesem Beispiel wird angenommen, dass in der aktuellen benutzerdefinierten Lösung die aktuellen und die historischen Daten auf zwei getrennte Benutzertabellen aufgeteilt sind (ProjectTaskCurrent und ProjectTaskHistory).

Wenn Ihre vorhandene Lösung sowohl die aktuellen als auch die historischen Zeilen in einer einzigen Tabelle speichert, sollten Sie die Daten auf zwei Tabellen aufteilen, bevor Sie die in folgendem Beispiel gezeigten Migrationsschritte ausführen. Legen Sie zuerst den Trigger auf der zukünftigen temporären Tabelle ab. Stellen Sie dann sicher, dass die PERIOD-Spalten keine NULL-Werte zulassen.

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

/* Make sure that 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
    )
);

Hinweise

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.

Durch das Hinzufügen von PERIOD wird für die aktuelle Tabelle eine Datenkonsistenzprüfung durchgeführt, um sicherzustellen, dass die vorhandenen Werte für Zeitraumspalten 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 ausgeblendete Spalten bevorzugt werden, verwenden Sie den Befehl ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Zugehöriger Inhalt