Erstellen einer temporären Tabelle mit Systemversionsverwaltung
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 alsGENERATED ALWAYS AS ROW START
oderGENERATED ALWAYS AS ROW END
deklariert sind.Es
PERIOD
wird immer davon ausgegangen, dass die Spalten nicht nullfähig sind, auch wenn die NULL-Zulässigkeit nicht angegeben ist. Wenn diePERIOD
Spalten explizit als NULL-zulässig definiert sind, schlägt dieCREATE 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 dieCREATE 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
oder9999-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_VERSIONING
eine 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 angebenDATA_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 ändertgenerated_always_type
sich implizit aufAS_ROW_START
undAS_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
- Temporale Tabellen
- Erste Schritte mit temporalen Tabellen mit Systemversion
- Verwalten der Aufbewahrung von Verlaufsdaten in temporalen Tabellen mit Systemversion
- Temporale Tabellen mit Systemversionsverwaltung und speicheroptimierten Tabellen
- CREATE TABLE (Transact-SQL)
- Ändern von Daten in einer temporalen Tabelle mit Systemversion
- Abfragen von Daten in einer temporalen Tabelle mit Systemversion
- Ändern des Schemas einer temporalen Tabelle mit Systemversion
- Beenden der Systemversionsverwaltung für eine temporale Tabelle mit Systemversion
Feedback
https://aka.ms/ContentUserFeedback.
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unterFeedback senden und anzeigen für