Query sui dati in una tabella temporale con controllo delle versioni di sistema

  • Articolo

Si applica a: SQL Server 2016 (13.x) e alle sue versioni successive Database SQL di Azure Istanza gestita di SQL di Azure

Quando è necessario recuperare lo stato corrente più recente dei dati in una tabella temporale, è possibile eseguire query nello stesso modo valido per l'esecuzione di query in tabelle non temporali. Se le colonne PERIOD non sono nascoste, i rispettivi valori compaiono in una query SELECT *. Se le colonne PERIOD sono state specificate come HIDDEN, i rispettivi valori non vengono visualizzati in una query SELECT *. Se le colonne PERIOD sono nascoste, è possibile fare riferimento in modo specifico alle colonne PERIOD nella clausola SELECT per restituire i relativi valori.

Per eseguire qualsiasi tipo di analisi basata sul tempo, usare la nuova clausola FOR SYSTEM_TIME con quattro sottoclausole specifiche per i dati temporali per eseguire query sui dati nelle tabelle correnti e di cronologia. Per maggiori informazioni su queste clausole, vedere Tabelle temporali e clausola FROM più 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

È possibile specificare FOR SYSTEM_TIME in modo indipendente per ogni tabella in una query. La clausola può essere usata all'interno di espressioni di tabella comuni, funzioni con valore di tabella e stored procedure. Quando si usa un alias di tabella con una tabella temporale, la clausola FOR SYSTEM_TIME deve essere inclusa tra il nome della tabella temporale e l'alias. Vedere di seguito il secondo esempio in Query per una data/ora specifica con la sottoclausola AS OF.

Query per un data/ora specifica con la sottoclausola AS OF

Usare la sottoclausola AS OF quando è necessario ricostruire lo stato dei dati esistente in un momento specifico nel passato. È possibile ricostruire i dati con la precisione del tipo datetime2 specificato nelle definizioni di colonna PERIOD.

La sottoclausola AS OF può essere usata con valori letterali costanti o variabili, il che consente di specificare in modo dinamico la condizione temporale. I valori specificati vengono interpretati come ora UTC.

Questo primo esempio restituisce lo stato della tabella dbo.Department in riferimento (AS OF) a una data specifica nel passato.

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

Questo secondo esempio confronta i valori tra due punti nel tempo per un subset di righe.

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;

Usare le viste con la sottoclausola AS OF nelle query temporali

L'uso delle viste risulta utile in scenari che richiedono analisi temporizzate complesse. Un esempio comune è la generazione di un report aziendale con i valori per il mese precedente.

I clienti si affidano in genere a un modello di database normalizzato che include molte tabelle con relazioni di chiave esterna. Può essere molto complicato risalire allo stato dei dati in un punto nel passato da questo modello normalizzato, in quanto tutte le tabelle cambiano in modo indipendente, ognuna con un ritmo proprio.

In questo caso, la soluzione migliore consiste nel creare una vista e applicare la sottoclausola AS OF all'intera vista. Questo approccio consente di separare la modellazione del livello di accesso ai dati dall'analisi temporizzata, perché SQL Server applica la clausola AS OF in modo trasparente a tutte le tabelle temporali che fanno parte della definizione della vista. Inoltre, è possibile combinare tabelle temporali e non temporali nella stessa vista e la clausola AS OF viene applicata solo a quelle temporali. Se la vista non fa riferimento ad almeno una tabella temporale, l'applicazione di clausole di query temporali ha esito negativo con un errore.

Il codice di esempio seguente crea una vista che unisce tre tabelle temporali: Department, CompanyLocation e 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

È ora possibile eseguire una query sulla vista usando la sottoclausola AS OF e un valore letterale datetime2:

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

Query per le modifiche apportate a colonne specifiche nel tempo

Le sottoclausole temporali FROM ... TO, BETWEEN ... AND e CONTAINED IN sono utili quando è necessario recuperare tutte le modifiche cronologiche per una riga specifica nella tabella corrente (controllo dei dati).

Le prime due sottoclausole restituiscono le versioni di riga che si sovrappongono a un periodo specificato (ovvero quelle con inizio prima del periodo specificato e fine dopo tale periodo), mentre CONTAINED IN restituisce solo le righe esistenti entro i limiti del periodo specificato.

Se è necessario cercare solo versioni di riga non aggiornate, è consigliabile eseguire la query direttamente sulla tabella di cronologia per le migliori prestazioni di query. Usare ALL quando è necessario eseguire query sui dati correnti e cronologici senza restrizioni.

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

Contenuto correlato