Abfragen von Daten in einer temporalen Tabelle mit Systemversionsverwaltung

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-Datenbank Azure 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 für PERIOD-Spalten HIDDEN festgelegt haben, werden ihre Werte in einer SELECT *-Abfrage nicht 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 Temporale Tabellen und FROM-Klausel plus JOIN, APPLY, PIVOT

  • 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. Bei Verwendung eines Tabellenalias mit einer temporalen Tabelle muss die FOR SYSTEM_TIME-Klausel zwischen dem Namen der temporalen Tabelle und dem Alias eingefügt werden (siehe zweites Beispiel unter Abfrage für einen bestimmten Zeitpunkt mithilfe der AS OF-Unterklausel).

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-Unterklausel kann mit konstanten Literalen oder mit Variablen verwendet werden, damit 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 der AS OF-Unterklausel in Abfragen für temporale Tabellen

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 dieser Vorgehensweise können Sie die Modellierung der Datenzugriffsebene von der Point-in-Time-Analyse entkoppeln, da SQL Server die AS OF-Klausel transparent auf alle temporalen Tabellen anwendet, die an der Ansichtsdefinition beteiligt sind. Darüber hinaus können Sie temporale Tabellen in derselben Ansicht mit nicht temporalen Tabellen kombinieren, und AS OF wird nur auf die temporalen Tabellen angewendet. Wenn die Ansicht nicht mindestens auf eine temporale Tabelle verweist, tritt bei der Anwendung von temporalen Abfrageklauseln ein Fehler auf.

Der folgende Beispielcode erstellt eine Ansicht, in der drei temporale Tabellen verknüpft werden: 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 mithilfe der AS OF-Unterklausel und eines datetime2-Literals abfragen:

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

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 die beste 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;

Zugehöriger Inhalt