Tabelle temporali

  • Articolo

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

Le tabelle temporali, note anche come tabelle temporali con controllo delle versioni del sistema, sono una funzionalità di database che offre un supporto predefinito per la gestione di informazioni sui dati archiviati nella tabella in qualsiasi momento anziché solo sui dati che risultano corretti nel momento attuale.

È possibile iniziare a usare tabelle temporali con controllo delle versioni di sistema ed esaminare gli scenari di utilizzo delle tabelle temporali.

Che cos'è una tabella temporale con controllo delle versioni di sistema?

Una tabella temporale con controllo delle versioni di sistema è un tipo di tabella utente progettato per mantenere una cronologia completa delle modifiche dei dati per semplificare l'analisi temporizzata. Questo tipo di tabella temporale è nota come tabella temporale con controllo delle versioni del sistema perché il sistema gestisce il periodo di validità per ogni riga (ossia il motore di database).

Ogni tabella temporale ha due colonne definite in modo esplicito, ciascuna con un tipo di dati datetime2 . Queste colonne sono note come colonne periodo. Le colonne periodo vengono usate esclusivamente dal sistema per registrare il periodo di validità per ciascuna riga ogni volta che una riga viene modificata. La tabella principale che archivia i dati correnti viene definita tabella corrente o semplicemente tabella temporale.

Oltre alle colonne periodo, una tabella temporale contiene anche un riferimento a un'altra tabella con schema con mirroring, definita tabella di cronologia. Il sistema usa la tabella di cronologia per archiviare automaticamente la versione precedente della riga ogni volta che una riga della tabella temporale viene aggiornata o eliminata. Durante la creazione di una tabella temporale è possibile specificare una tabella di cronologia esistente, che deve essere conforme allo schema, oppure consentire al sistema di creare una tabella di cronologia predefinita.

Perché temporale?

Le origini dati reali sono dinamiche e quasi sempre le decisioni aziendali si basano su approfondimenti che gli analisti ricavano dall'evoluzione dei dati. Alcuni casi d'uso delle tabelle temporali:

  • Controllo di tutte le modifiche dei dati ed esecuzione di analisi forensi, se necessario
  • Ricostruzione dello stato dei dati in qualsiasi momento trascorso
  • Calcolo delle tendenze nel tempo
  • Gestione di una dimensione a modifica lenta per le applicazioni di supporto decisionale
  • Recupero da modifiche accidentali dei dati ed errori delle applicazioni

Come funziona una tabella temporale?

Il controllo delle versioni di sistema per una tabella viene implementato come una coppia di tabelle, una tabella corrente e una tabella di cronologia. All'interno di ogni tabella vengono usate due colonne datetime2 extra per definire il periodo di validità per ogni riga:

  • Colonna di inizio periodo: il sistema registra l'ora di inizio per la riga in questa colonna, in genere indicata come colonna ValidFrom.

  • Colonna di fine periodo: il sistema registra l'ora di fine per la riga in questa colonna, in genere indicata come colonna ValidTo.

La tabella corrente contiene il valore corrente per ogni riga. La tabella di cronologia contiene ogni valore precedente (versione precedente) per ogni riga, se presente, e l'ora di inizio e di fine del relativo periodo di validità.

Diagramma che mostra il funzionamento di una tabella temporale.

Lo script seguente illustra uno scenario con informazioni sui dipendenti:

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 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Per altre informazioni, vedere Creazione di una tabella temporale con controllo delle versioni di sistema.

  • Inserti: il sistema imposta il valore per la colonna ValidFrom sul momento di avvio della transazione corrente (fuso orario UTC) in base al clock di sistema e assegna come valore per la colonna ValidTo il valore massimo 9999-12-31. In questo modo la riga viene contrassegnata come aperta.

  • Aggiornamenti: il sistema archivia il valore precedente del record nella tabella di cronologia e imposta il valore per la colonna ValidTo sull'ora di inizio della transazione corrente (fuso orario UTC) in base al clock di sistema. In questo modo la riga viene contrassegnata come chiusa, con un periodo registrato in cui risultava valida. Nella tabella corrente la riga viene aggiornata con il nuovo valore e il sistema imposta il valore per la colonna ValidFrom sul momento di avvio della transazione (fuso orario UTC) in base al clock di sistema. Il valore per la riga aggiornata nella tabella corrente per la colonna ValidTo rimane il valore massimo di 9999-12-31.

  • Eliminare: il sistema archivia il valore precedente del record nella tabella di cronologia e imposta il valore per la colonna ValidTo sul momento di avvio della transazione corrente (fuso orario UTC) in base al clock di sistema. In questo modo la riga viene contrassegnata come chiusa, con un periodo registrato in cui la riga precedente risultava valida. Nella tabella corrente la riga viene rimossa. Le query della tabella corrente non restituiscono questa riga. Solo le query che gestiscono i dati di cronologia restituiscono dati per i quali viene chiusa una riga.

  • Unire: l'operazione si comporta esattamente come se venissero eseguite fino a tre istruzioni (INSERT, UPDATE e/o DELETE), in base alle azioni specificate nell'istruzione MERGE.

I tempi registrati nelle colonne datetime2 del sistema sono basati sull'ora di inizio della transazione stessa. Ad esempio, tutte le righe inserite all'interno di una singola transazione avranno lo stesso orario UTC registrato nella colonna corrispondente all'inizio del periodo SYSTEM_TIME.

Quando si eseguono query di modifica dei dati in una tabella temporale, il motore di database aggiunge una riga alla tabella di cronologia anche se non viene modificato alcun valore di colonna.

Come si esegue una query sui dati temporali?

L'istruzione SELECT ... FROM <table> ha una nuova clausola FOR SYSTEM_TIME con cinque sottoclausole specifiche per i dati temporali per eseguire query sui dati nelle tabelle correnti e di cronologia. La nuova sintassi dell'istruzione SELECT è supportata direttamente su una singola tabella, propagata attraverso diversi join e viste su più tabelle temporali.

Quando si esegue una query utilizzando la clausola FOR SYSTEM_TIME con una delle cinque sottoclausole, vengono inclusi i dati storici della tabella temporale, come mostrato nell'immagine seguente.

Diagramma che mostra il funzionamento delle query temporali.

La query seguente cerca con la condizione di filtro WHERE EmployeeID = 1000 le versioni di riga per un dipendente che erano attive almeno per una parte del periodo compreso tra il 1° gennaio 2021 e il 1° gennaio 2022, incluso il limite superiore:

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME esclude le righe che hanno un periodo di validità con durata pari a zero (ValidFrom = ValidTo).

Tali righe vengono generate se si eseguono più aggiornamenti sulla stessa chiave primaria nell'ambito della stessa transazione. In tale caso l'esecuzione di query temporali restituisce solo le versioni di righe prima delle transazioni e le righe correnti dopo le transazioni.

Se è necessario includere le righe nell'analisi, eseguire la query direttamente nella tabella di cronologia.

Nella seguente tabella il valore ValidFrom della colonna delle righe risultanti rappresenta il valore presente nella colonna ValidFrom della tabella su cui si esegue la query e ValidTo rappresenta il valore presente nella colonna ValidTo della tabella su cui si esegue la query. Per la sintassi completa e per esempi, vedere FROM condizione più JOIN, APPLY, PIVOT e Query sui dati in una tabella temporale con controllo delle versioni di sistema.

Espressione Righe risultanti Nota
AS OF date_time ValidFrom <= date_time AND ValidTo > date_time Restituisce una tabella con una righe contenenti i valori che erano correnti in un momento specificato nel passato. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia. I risultati vengono filtrati in modo da restituire i valori nella riga valida alla data e all'ora specificate nel parametro date_time. Il valore di una riga viene considerato valido se il valore system_start_time_column_name è minore o uguale al valore del parametro date_time e il valore system_end_time_column_name è maggiore del valore del parametro date_time.
FROM start_date_time TO end_date_time ValidFrom < end_date_time AND ValidTo > start_date_time Restituisce una tabella con i valori per tutte le versioni di riga che erano attive nell'intervallo di tempo specificato, indipendentemente dal fatto che abbiano iniziato ad essere attive prima del valore del parametro start_date_time per l'argomento FROM o abbiano cessato di essere attive dopo il valore del parametro end_date_time per l'argomento TO. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia. I risultati vengono filtrati in modo da restituire i valori per tutte le versioni di riga che erano attive in qualsiasi momento durante l'intervallo di tempo specificato. Le righe che non sono più state attive esattamente in corrispondenza del limite inferiore definito dall'endpoint FROM non sono incluse e le righe diventate attive esattamente in corrispondenza del limite superiore definito dall'endpoint TO non sono incluse.
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time Come sopra nella descrizione FOR SYSTEM_TIME FROM start_date_time TO end_date_time, tranne che la tabella delle righe restituite include le righe che sono diventate attive sul limite superiore definito dall'end_date_time endpoint.
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_time AND ValidTo <= end_date_time Restituisce una tabella con i valori per tutte le versioni di riga che sono state aperte e chiuse nell'intervallo di tempo specificato, definito dai due valori di periodo per l'argomento CONTAINED IN. Sono incluse le righe diventate attive esattamente in corrispondenza del limite inferiore o che non sono più state attive esattamente in corrispondenza del limite superiore.
ALL Tutte le righe Restituisce l'unione di righe che appartengono alla tabella corrente e a quella di cronologia.

Nascondere le colonne di periodo

È possibile scegliere di nascondere le colonne di periodo, in modo che le query che non fanno riferimento in modo esplicito a queste colonne non le restituiscano, ad esempio quando si esegue SELECT * FROM <table>.

Per restituire una colonna nascosta, è necessario fare riferimento in modo esplicito alla colonna nella query. Allo stesso modo, le istruzioni INSERT e BULK INSERT continueranno come se le nuove colonne periodo non fossero presenti e i valori delle colonne vengono popolati automaticamente.

Per informazioni dettagliate sull'uso della clausola HIDDEN, vedere CREATE TABLE e ALTER TABLE.

Esempi

Contenuto correlato