Erstellen einer temporären Tabelle mit Systemversionsverwaltung

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure 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 temporären 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. Dies ist auch die einfachste Möglichkeit, eine temporale Tabelle zu erstellen, da sie keinen Parameter in der SYSTEM_VERSIONING -Klausel erfordert. 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);

Bemerkungen

  • Für eine temporale Tabelle mit Systemversion muss ein Primärschlüssel definiert sein 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 ENDdeklariert sind.

  • Es PERIOD wird immer davon ausgegangen, dass die Spalten nicht nullfähig sind, auch wenn die NULL-Zulässigkeit nicht angegeben ist. Wenn die PERIOD Spalten explizit als NULL-zulässig definiert sind, schlägt die CREATE TABLE Anweisung fehl.

  • Die Verlaufstabelle muss immer schemabündig mit der aktuellen oder temporalen Tabelle in Bezug auf die Anzahl von Spalten, Spaltennamen, Reihenfolgen und Datentypen ausgerichtet 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_<Objekt-ID_der_aktuellen_temporalen_Tabelle>_[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. Die PAGE-Komprimierung wird nach Möglichkeit angewendet, andernfalls wird die Verlaufstabelle unkomprimiert. 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_<Verlaufstabellenname> 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 und speicheroptimierten Tabellen.

Erstellen einer temporären Tabelle mit Standardverlaufstabelle

Das Erstellen einer temporalen Tabelle mit einer Standardverlaufstabelle 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 ist.

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 HISTORY_TABLE Parameter obligatorisch.
  • Wenn das angegebene Schema nicht vorhanden ist, schlägt die CREATE TABLE Anweisung fehl.
  • Wenn die durch den HISTORY_TABLE Parameter angegebene Tabelle bereits vorhanden ist, wird sie anhand der neu erstellten temporalen Tabelle in Bezug auf Schemakonsistenz und temporale Datenkonsistenz überprüft. Wenn Sie eine ungültige Verlaufstabelle angeben, schlägt die CREATE TABLE Anweisung fehl.

Erstellen einer temporären Tabelle mit benutzerdefinierter Verlaufstabelle

Das Erstellen einer temporären 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 an der erstellten temporalen Tabelle ausgerichtet 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 mit Systemversionsverwaltung unter Angabe der benutzerdefinierten Verlaufstabelle als Standardverlaufstabelle erstellt.

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 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 der primäre Anwendungsfall die Datenüberwachung ist (d. h. wenn Sie Verlaufsänderungen für eine bestimmte Zeile in der aktuellen Tabelle suchen möchten), empfiehlt sich die Erstellung einer Rowstoreverlaufstabelle mit einem gruppierten Index.
  • 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 Transaktions- oder Mergereplikation konfiguriert werden.

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

Sie können die Systemversionsverwaltung für eine vorhandene nicht temporale Tabelle aktivieren, z. B. wenn Sie eine benutzerdefinierte temporale Lösung zur integrierten 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

Erwägen Sie beim Konvertieren einer vorhandenen Tabelle die Verwendung der HIDDEN -Klausel, um die neuen PERIOD Spalten (die datetime2-SpaltenValidFrom und ValidTo) auszublenden, um Auswirkungen auf vorhandene Anwendungen zu vermeiden, SELECT * die nicht explizit Spaltennamen angeben (z. B. oder INSERT ohne Spaltenliste), die nicht für die Verarbeitung neuer Spalten konzipiert sind.

Hinzufügen der Versionsverwaltung zu nicht temporären Tabellen

Wenn Sie mit der Nachverfolgung von Änderungen für eine nicht temporale Tabelle beginnen möchten, die die Daten enthält, 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 an der Genauigkeit für die zugrunde liegende Tabelle ausgerichtet sein. Weitere Informationen finden Sie in den folgenden Hinweisen.

Bemerkungen

  • Das Hinzufügen von Spalten, die keine Nullwerte zulassen, mit Standardwerten einer vorhandenen Tabelle mit Daten ist ein Vorgang zur Datengröße für alle Editionen außer SQL Server Enterprise Edition (für die es sich um einen Metadatenvorgang handelt). Bei einer großen vorhandenen Verlaufstabelle mit Daten in SQL Server Standard Edition kann das Hinzufügen einer Spalte ungleich NULL ein teurer 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.
  • Das Hinzufügen PERIOD führt eine Datenkonsistenzprüfung für die aktuelle Tabelle durch, um sicherzustellen, dass die vorhandenen Werte für Periodenspalten gültig sind.
  • Wenn beim Aktivieren SYSTEM_VERSIONINGeine vorhandene Verlaufstabelle angegeben wird, wird eine Datenkonsistenzprüfung sowohl für die aktuelle als auch für die Verlaufstabelle durchgeführt. Sie kann übersprungen werden, wenn Sie als zusätzlichen Parameter angeben DATA_CONSISTENCY_CHECK = OFF .

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 nicht nullable sind.

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

Bemerkungen

  • Das Verweisen auf vorhandene Spalten in der PERIOD Definition ändert generated_always_type sich implizit auf AS_ROW_START und AS_ROW_END für diese Spalten.
  • Durch hinzufügen PERIOD wird eine Datenkonsistenzprüfung für die aktuelle Tabelle durchgeführt, um sicherzustellen, dass die vorhandenen Werte für Periodenspalten gültig sind.
  • Es wird dringend empfohlen, mit DATA_CONSISTENCY_CHECK = ON festzulegenSYSTEM_VERSIONING, um Datenkonsistenzprüfungen für vorhandene Daten zu erzwingen.
  • Wenn ausgeblendete Spalten bevorzugt werden, verwenden Sie den Befehl ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Nächste Schritte