Abfragen von Daten in einer temporalen Tabelle mit Systemversion

  • Artikel

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 HIDDENangegeben 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, CompanyLocationund 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 ZONEabfragen:

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