Abfragen von Daten in einer temporalen Tabelle mit Systemversion
Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed Instance
Wenn Sie den letzten (aktuellen) Zustand der Daten in einer temporalen Tabelle abrufen möchten, können Sie die Abfrage wie bei nicht temporalen Tabellen durchführen. Wenn die PERIOD
Spalten nicht ausgeblendet sind, werden ihre Werte in einer SELECT *
Abfrage angezeigt. Wenn Sie Spalten als HIDDEN
angegeben PERIOD
haben, werden ihre Werte nicht in einer SELECT *
Abfrage angezeigt. Wenn die PERIOD
-Spalten ausgeblendet werden, müssen Sie explizit auf die PERIOD
-Spalten in der SELECT
-Klausel hinweisen, damit die Werte für diese Spalten zurückgegeben werden.
Verwenden Sie bei jedweder Art von zeitbasierter Analyse die neue FOR SYSTEM_TIME
-Klausel mit vier für temporale Tabellen spezifischen Unterklauseln, um Daten in den aktuellen Tabellen und den Verlaufstabellen abzufragen. Weitere Informationen zu diesen Klauseln finden Sie unter Temporäre Tabellen und FROM (Transact-SQL).
AS OF <date_time>
FROM <start_date_time> TO <end_date_time>
BETWEEN <start_date_time> AND <end_date_time>
CONTAINED IN (<start_date_time>, <end_date_time>)
ALL
FOR SYSTEM_TIME
kann unabhängig für jede Tabelle in einer Abfrage angegeben werden. Dieses Element kann in allgemeinen Tabellenausdrücken, Tabellenwertfunktionen und gespeicherten Prozeduren verwendet werden. Wenn Sie einen Tabellenalias mit einer temporalen Tabelle verwenden, muss die FOR SYSTEM_TIME
-Klausel zwischen dem Temporaltabellennamen und dem Alias enthalten sein (siehe Abfrage für einen bestimmten Zeitpunkt mit der AS OF
Unterklassier - zweites Beispiel).
Abfrage für einen bestimmten Zeitpunkt mithilfe der AS OF
-Unterklausel
Verwenden Sie die AS OF
-Unterklausel, wenn Sie den Zustand der Daten wiederherstellen möchten, wie er zu einem bestimmten Zeitpunkt in der Vergangenheit war. Sie können die Daten mit der Genauigkeit vom datetime2-Typ rekonstruieren, der in den Spaltendefinitionen von PERIOD
angegeben war.
Die AS OF
Unterclause kann mit konstanten Literalen oder mit Variablen verwendet werden, sodass Sie die Zeitbedingung dynamisch angeben können. Die bereitgestellten Werte werden als UTC-Zeit interpretiert.
Dieses erste Beispiel gibt den Zustand der Tabelle „dbo.Department“ zu einem bestimmen Zeitpunkt (AS OF
) in der Vergangenheit zurück.
/*State of entire table AS OF specific date in the past*/
SELECT [DeptID],
[DeptName],
[ValidFrom],
[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';
Dieses zweite Beispiel vergleicht die Werte zwischen zwei Zeitpunkten für eine Teilmenge von Zeilen.
DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(day, -1, sysutcdatetime());
/*Comparison between two points in time for subset of rows*/
SELECT D_1_Ago.[DeptID],
D.[DeptID],
D_1_Ago.[DeptName],
D.[DeptName],
D_1_Ago.[ValidFrom],
D.[ValidFrom],
D_1_Ago.[ValidTo],
D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
ON D_1_Ago.[DeptID] = [D].[DeptID]
AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;
Verwenden von Ansichten mit AS OF
Unterclause in temporalen Abfragen
Das Verwenden von Ansichten ist nützlich in Szenarios, in denen komplexe Point-in-Time-Analysen erforderlich sind. Ein gängiges Beispiel ist die Generierung eines Geschäftsberichts zum aktuellen Zeitpunkt mit den Werten des letzten Monats.
In der Regel besitzen Kund*innen ein normalisiertes Datenbankmodell, das viele Tabellen mit Fremdschlüsselbeziehungen enthält. Es kann sich schwierig gestalten, herauszufinden, wie Daten aus diesem normalisierten Modell zu einem Zeitpunkt in der Vergangenheit ausgesehen haben, da alle Tabellen sich unabhängig voneinander und in ihrem eigenen Rhythmus ändern.
In diesem Fall besteht die beste Option darin, eine Ansicht zu erstellen und die AS OF
-Unterklausel auf die komplette Ansicht anzuwenden. Mit diesem Ansatz können Sie die Modellierung der Datenzugriffsebene von der Point-in-Time-Analyse entkoppeln, da SQL Server Klausel transparent auf alle temporalen Tabellen anwendetAS OF
, die an der Ansichtsdefinition teilnehmen. Darüber hinaus können Sie temporale mit nicht temporalen Tabellen in derselben Ansicht kombinieren und AS OF
werden nur auf temporale Tabellen angewendet. Wenn die Ansicht nicht auf mindestens eine temporale Tabelle verweist, schlägt das Anwenden von temporalen Abfrageklauseln auf sie mit einem Fehler fehl.
Der folgende Beispielcode erstellt eine Ansicht, die drei temporale Tabellen verknüpft: Department
, CompanyLocation
und LocationDepartments
:
CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
[CompanyLocation].LocName,
[CompanyLocation].City,
[Department].DeptID,
[Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
ON LocationDepartments.DeptID = [Department].DeptID;
GO
Sie können die Ansicht jetzt mit der AS OF
Unterclause und einem datetime2-Literal abfragen:
/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF'2021-09-01 T10:00:00.7230011';
Alternativ können Sie die Ansicht mithilfe der AS OF
Unterclause mit einer lokalen Zeitzone und AT TIME ZONE
abfragen:
/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';
Abfragen von Änderungen an bestimmten Zeilen über einen Zeitraum
Die temporalen Unterklauseln FROM ... TO
, BETWEEN ... AND
und CONTAINED IN
sind nützlich, wenn Sie alle Verlaufsänderungen für eine bestimmte Zeile in der aktuellen Tabelle abrufen müssen (auch bekannt als Datenüberwachung).
Die ersten beiden Unterklauseln geben Zeilenversionen zurück, die sich mit einem bestimmten Zeitraum überschneiden (d. h. die Zeilenversionen, die vor dem angegebene Zeitraum begannen und danach endeten), wohingegen CONTAINED IN
nur die Zeilenversionen zurückgibt, die innerhalb bestimmter Zeitraumgrenzen vorhanden waren.
Wenn Sie nur nach nicht aktuellen Zeilenversionen suchen, sollten Sie die Verlaufstabelle direkt abfragen, um eine optimale Abfrageleistung zu erzielen. Verwenden Sie ALL
, wenn Sie aktuelle Daten und Verlaufsdaten ohne Einschränkungen abfragen müssen.
/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
[DeptName],
[ValidFrom],
[ValidTo],
IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;
/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
[DeptName],
[ValidFrom],
[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;
/* Query using ALL sub-clause */
SELECT [DeptID],
[DeptName],
[ValidFrom],
[ValidTo],
IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
[ValidFrom] DESC;
Nächste Schritte
- Temporale Tabellen
- FROM (Transact-SQL)
- AT TIME ZONE (Transact-SQL)
- Erstellen einer temporalen Tabelle mit Systemversion
- Ändern 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