Temporale TabellenTemporal Tables

Gilt für: JaSQL Server JaAzure SQL-Datenbank NeinAzure Synapse Analytics (SQL DW) NeinParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

In SQL Server 2016 wurde die Unterstützung von temporäre Tabellen (auch als temporäre Tabellen mit Systemversionsverwaltung bezeichnet) als Datenbankfeature eingeführt, das integrierte Unterstützung für das Bereitstellen von Informationen zu den zu jedem Zeitpunkt in der Tabelle gespeicherten Daten zur Verfügung stellt, anstatt nur die aktuell in einer Tabelle gespeicherten Daten zu unterstützen.SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal ist ein Datenbankfeature, das zusammen mit ANSI SQL 2011 eingeführt wurde.Temporal is a database feature that was introduced in ANSI SQL 2011.

SchnellstartQuick Start

Was ist eine temporale Tabelle mit Versionsverwaltung durch das System?What is a system-versioned temporal table?

Eine temporale Tabelle mit Systemversionsverwaltung ist ein Benutzertabellentyp, der darauf ausgelegt ist, den Verlauf aller Datenänderungen lückenlos zu speichern und einfache Zeitpunktanalysen zu ermöglichen.A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Bei diesem Typ von temporaler Tabelle spricht man von Versionsverwaltung durch das System, da die Gültigkeitsdauer für jede Zeile vom System (d.h. von der Datenbank-Engine) verwaltet wird.This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

Jede temporale Tabelle weist zwei explizit definierte Spalten auf, beide vom Datentyp datetime2 .Every temporal table has two explicitly defined columns, each with a datetime2 data type. Diese Spalten werden als Zeitraumspalten bezeichnet.These columns are referred to as period columns. Diese Zeitraumspalten werden bei jeder Änderung einer Zeile ausschließlich vom System zum Aufzeichnen des Gültigkeitszeitraums verwendet.These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

Über diese Zeitraumspalten hinaus enthält eine temporale Tabelle außerdem einen Verweis auf eine weitere Tabelle mit einem gespiegelten Schema.In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. Das System verwendet diese Tabelle, um bei jeder Aktualisierung oder Löschung einer Zeile in der temporalen Tabelle automatisch die Vorversion der Zeile zu speichern.The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Diese zusätzliche Tabelle wird als die Verlaufstabelle bezeichnet, während die Haupttabelle, die die aktuellen (gültigen) Zeilenversionen speichert, als die aktuelle Tabelle oder einfach als die temporale Tabelle bezeichnet wird.This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. Während der Erstellung von temporalen Tabellen können Benutzer eine vorhandene Verlaufstabelle (deren Schema kompatibel sein muss) angeben oder vom System eine standardmäßige Verlaufstabelle erstellen lassen.During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

Warum temporal?Why temporal?

Real verwendete Datenquellen sind dynamisch, und in der Mehrzahl der Fälle beruhen Geschäftsentscheidungen auf Erkenntnissen, die Analysten aus der Entwicklung der Daten ableiten.Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Zu den Einsatzgebieten von temporalen Tabellen zählen beispielsweise:Use cases for temporal tables include:

  • Die Überwachung aller Datenänderungen und ggf. die Ausführung von DatenforensikAuditing all data changes and performing data forensics when necessary

  • Wiederherstellung des Status der Daten zu beliebigen Zeitpunkten in der VergangenheitReconstructing state of the data as of any time in the past

  • Berechnen von Trends im zeitlichen VerlaufCalculating trends over time

  • Warten einer sich langsam verändernden Dimension für Anwendungen zur EntscheidungsunterstützungMaintaining a slowly changing dimension for decision support applications

  • Wiederherstellung nach unbeabsichtigten Datenänderungen und AnwendungsfehlernRecovering from accidental data changes and application errors

Wie funktioniert temporal?How does temporal work?

Die Versionsverwaltung durch das System für eine Tabelle wird in Form eines Tabellenpaars implementiert, von denen eine die aktuelle und die andere eine Verlaufstabelle ist.System-versioning for a table is implemented as a pair of tables, a current table and a history table. Innerhalb jeder dieser Tabellen werden die zwei folgenden zusätzlichen datetime2 -Spalten verwendet, um den Gültigkeitszeitraum für jede Zeile zu definieren:Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Spalte mit Systemstartzeit: Das System zeichnet die Startzeit für die Zeile in dieser Spalte auf, normalerweise als SysStartTime-Spalte bezeichnet.Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

  • Spalte mit Systemendzeitpunkt: Das System zeichnet den Endzeitpunkt für die Zeile in dieser Spalte auf, normalerweise als SysEndTime-Spalte bezeichnet.Period end column: The system records the end time for the row in this column, typically denoted as the SysEndTime column.

Die aktuelle Tabelle enthält den aktuellen Wert für jede Zeile.The current table contains the current value for each row. Die Verlaufstabelle enthält jeden früheren Wert für jede Zeile, falls vorhanden, sowie die Anfangszeit und Endzeit für den Zeitraum, für den er gültig war.The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

Temporäre Tabelle-FunktionsweiseTemporal-HowWorks

Das folgende einfache Beispiel veranschaulicht ein Szenario mit Mitarbeiterinformationen in einer hypothetischen Personaldatenbank:The following simple example illustrates a scenario with Employee information in hypothetical HR database:

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

INSERT-Vorgänge: Bei einem INSERT-Vorgang legt das System den Wert für die SysStartTime-Spalte basierend auf der Systemzeit auf die Startzeit der aktuellen Transaktion (in der UTC-Zeitzone) fest und weist der SysEndTime-Spalte den Maximalwert von 9999-12-31 zu.INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. Dadurch wird die Zeile als offen gekennzeichnet.This marks the row as open.

UPDATE-Vorgänge: Bei einem UPDATE-Vorgang speichert das System den vorhergehenden Wert der Zeile in der Verlaufstabelle und legt den Wert für die SysEndTime-Spalte basierend auf der Systemzeit auf die Startzeit der aktuellen Transaktion (in der UTC-Zeitzone) fest.UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Dadurch wird die Zeile als geschlossen gekennzeichnet. Zudem wird der Zeitraum vermerkt, zu dem die Zeile gültig war.This marks the row as closed, with a period recorded for which the row was valid. In der aktuellen Tabelle wird die Zeile mit dem neuen Wert aktualisiert, und das System legt den Wert für die SysStartTime -Spalte basierend auf der Systemzeit auf den Anfangszeitpunkt der Transaktion (in der UTC-Zeitzone) fest.In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. Der Wert für die SysEndTime -Spalte für die aktualisierte Zeile verbleibt in der aktuellen Tabelle auf dem Maximalwert von 9999-12-31.The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.

DELETE-Vorgänge: Bei einem DELETE-Vorgang speichert das System den vorhergehenden Wert der Zeile in der Verlaufstabelle und legt den Wert für die SysEndTime-Spalte basierend auf der Systemzeit auf die Startzeit der aktuellen Transaktion (in der UTC-Zeitzone) fest.DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. Dadurch wird die Zeile als geschlossen gekennzeichnet. Zudem wird der Zeitraum vermerkt, zu dem die vorhergehende Zeile gültig war.This marks the row as closed, with a period recorded for which the previous row was valid. In der aktuellen Tabelle wird die Zeile entfernt.In the current table, the row is removed. Bei Abfragen der aktuellen Tabelle wird die Zeile nicht zurückgegeben.Queries of the current table will not return this row. Nur bei Abfragen von Verlaufsdaten werden Daten zurückgegeben, für die eine Zeile geschlossen ist.Only queries that deal with history data return data for which a row is closed.

MERGE-Vorgänge: Bei einem MERGE-Vorgang verläuft der Vorgang so, als würden bis zu drei Anweisungen ausgeführt (INSERT, UPDATE und/oder DELETE), je nachdem, welche Aktionen in der MERGE-Anweisung angegeben sind.MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

Wichtig

Die in den datetime2-Spalten des Systems aufgezeichneten Zeiten basieren auf dem Anfangszeitpunkt der Transaktion selbst.The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. Beispielsweise wird für alle Zeilen, die innerhalb einer einzelnen Transaktion eingefügt werden, in der Spalte, die dem Beginn des SYSTEM_TIME -Zeitraums entspricht, die gleiche UTC-Zeit aufgezeichnet.For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Wie lassen sich temporale Daten abfragen?How do I query temporal data?

Die FROM <Tabelle> -Klausel der SELECT-Anweisung enthält die neue Klausel FOR SYSTEM_TIME mit fünf zeitspezifischen Unterklauseln zum Abfragen von Daten in der aktuellen und in den Verlaufstabellen.The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. Die Syntax dieser neuen SELECT -Anweisung wird für Einzeltabellen direkt unterstützt, durch mehrfache JOINS weitergegeben und für zusammenfassende Sichten mehrerer temporaler Tabellen unterstützt.This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Zeitliche AbfragenTemporal-Querying

Die folgende Abfrage sucht nach Zeilenversionen für die Mitarbeiterzeile mit EmployeeID = 1000, die mindestens eine Zeit lang zwischen dem 1. Januar 2014 und dem 1. Januar 2015 (einschließlich der oberen Grenze) aktiv waren:The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

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;  

Hinweis

FOR SYSTEM_TIME filtert Zeilen mit einer Gültigkeitsdauer von 0 (null) (SysStartTime = SysEndTime).FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime).
Diese Zeilen werden generiert, wenn mehrere Updates für den gleichen Primärschlüssel innerhalb der gleichen Transaktion ausgeführt werden.Those rows will be generated if you perform multiple updates on the same primary key within the same transaction.
In diesem Fall geben temporale Abfragen nur Zeilenversionen vor diesen Transaktionen sowie solche zurück, die erst nach diesen Transaktionen wirksam wurden.In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions.
Wenn diese Zeilen in die Analyse mit aufgenommen werden müssen, fragen Sie die Verlaufstabelle direkt ab.If you need to include those rows in the analysis, query the history table directly.

In der Tabelle unten steht SysStartTime in der Spalte „Qualifizierte Zeilen“ für den Wert in der -Spalte SysStartTime in der abgefragten Tabelle, und SysEndTime stellt den Wert in der Spalte SysEndTime in der abgefragten Tabelle dar.In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. Die vollständige Syntax und Beispiele finden Sie unter FROM (Transact-SQL) und Abfragen von Daten in einer temporalen Tabelle mit Systemversionsverwaltungunterstützt wird.For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

expressionExpression Qualifizierte ZeilenQualifying Rows und BeschreibungDescription
AS OF<Datum_Uhrzeit>AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time Gibt eine Tabelle mit Zeilen zurück, die die Werte enthalten, die zum angegebenen Zeitpunkt in der Vergangenheit real (aktuell) waren.Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Intern wird eine Union zwischen der temporalen Tabelle und ihrer Verlaufstabelle ausgeführt, und die Ergebnisse werden so gefiltert, dass die Werte in der Zeile zurückgegeben werden, die zu dem durch den Parameter <Datum_Uhrzeit> angegebenen Zeitpunkt gültig waren.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. Der Wert für eine Zeile ist gültig, wenn der Wert system_start_time_column_name kleiner als oder gleich dem Parameterwert <Datum_Uhrzeit> und der Wert system_end_time_column_name größer als der Parameterwert <Datum_Uhrzeit> ist.The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.
FROM<Start_Datum_Zeit>TO<End_Datum_Zeit>FROM<start_date_time>TO<end_date_time> SysStartTime < Ende_Datum_Uhrzeit AND SysEndTime > Start_Datum_UhrzeitSysStartTime < end_date_time AND SysEndTime > start_date_time Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des angegebenen Zeitbereichs aktiv waren, unabhängig davon, ob ihre Aktivität vor dem <Start_Datum_Zeit> -Parameterwert für das FROM-Argument begonnen hat oder ihre Aktivität nach dem <End_Datum_Zeit> -Parameterwert für das TO-Argument geendet hat.Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. Intern wird eine Union zwischen der temporalen Tabelle und ihrer Verlaufstabelle ausgeführt, und die Ergebnisse werden so gefiltert, dass die Werte für alle Zeilenversionen zurückgegeben werden, die zu irgendeinem Zeitpunkt innerhalb des angegebenen Zeitbereichs aktiv waren.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Zeilen, die genau an dem durch den FROM-Endpunkt definierten unteren Grenzwert deaktiviert wurden, sind nicht enthalten, und Datensätze, die genau an dem durch den TO-Endpunkt definierten oberen Grenzwert aktiv wurden, sind auch nicht enthalten.Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<Start_Datum_Uhrzeit>AND<Ende_Datum_Uhrzeit>BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time Gleich wie oben in der Beschreibung zu FOR SYSTEM_TIME FROM <Start_Datum_Uhrzeit> TO <Ende_Datum_Uhrzeit>, mit dem Unterschied, dass die zurückgegebene Tabelle Zeilen enthält, die an dem durch den <Ende_Datum_Uhrzeit>-Endpunkt definierten oberen Grenzwert aktiv wurden.Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<Start_Datum_Uhrzeit>, <Ende_Datum_Uhrzeit>)CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= end_date_time Gibt eine Tabelle mit den Werten für alle Zeilenversionen zurück, die innerhalb des von den zwei Datums-/Uhrzeitwerten für das Argument CONTAINED IN definierten Zeitbereichs geöffnet und geschlossen wurden.Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Zeilen, die genau beim unteren Grenzwert aktiv wurden, oder deren Aktivität genau beim oberen Grenzwert endete, sind enthalten.Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALLALL Alle ZeilenAll rows Gibt die Vereinigungsmenge der Zeilen zurück, die der aktuellen und der Verlaufstabelle angehören.Returns the union of rows that belong to the current and the history table.

Hinweis

Optional können Sie diese Zeitraumspalten ausblenden, sodass sie von Abfragen, die nicht explizit auf diese Zeitraumspalten verweisen, nicht zurückgegeben werden (Szenario SELECT * FROM <Tabelle> ).Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT * FROM<table> scenario). Um eine ausgeblendete Spalte zurückzugeben, verweisen Sie einfach in der Abfrage explizit auf die ausgeblendete Spalte.To return a hidden column, simply explicitly refer to the hidden column in the query. Analog dazu verhalten sich INSERT und BULK INSERT -Anweisungen, als ob diese neuen Zeitraumspalten nicht vorhanden wären (und die Spaltenwerte werden automatisch aufgefüllt).Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). Weitere Informationen zur Verwendung der HIDDEN -Klausel finden Sie unter CREATE TABLE (Transact-SQL) und ALTER TABLE (Transact-SQL)unterstützt wird.For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

Weitere InformationenSee Also

Erste Schritte mit temporalen Tabellen mit Systemversionsverwaltung Getting Started with System-Versioned Temporal Tables
Temporale Tabellen mit Systemversionsverwaltung und speicheroptimierten Tabellen System-Versioned Temporal Tables with Memory-Optimized Tables
Verwendungsszenarien für temporale Tabellen Temporal Table Usage Scenarios
Überlegungen und Einschränkungen zu temporalen Tabellen Temporal Table Considerations and Limitations
Verwalten der Beibehaltung von Verlaufsdaten in temporalen Tabellen mit Systemversionsverwaltung Manage Retention of Historical Data in System-Versioned Temporal Tables
Partitionierung mit temporären Tabellen Partitioning with Temporal Tables
Systemkonsistenzprüfungen von temporalen Tabellen Temporal Table System Consistency Checks
Sicherheit bei temporale Tabellen Temporal Table Security
Metadatenansichten und Funktionen für temporale TabellenTemporal Table Metadata Views and Functions