Tabelle temporali

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

Le tabelle temporali (note anche come tabelle temporali versionate di sistema) sono una funzione di database che fornisce supporto integrato per le informazioni sui dati memorizzati nella tabella in qualsiasi momento, piuttosto che solo per i dati attuali.

Introduzione alle tabelle temporali con versione di sistema e consulta gli scenari d'uso delle tabelle temporali.

Che cos'è una tabella temporale con versioni gestite dal sistema?

Una tabella temporale versionata al sistema è un tipo di tabella utente progettata per mantenere una cronologia completa delle modifiche dei dati, consentendo l'analisi in un momento nel tempo. Questo tipo di tabella temporale è chiamato tabella temporale versionata al sistema, perché il sistema gestisce il periodo di validità per ogni riga (cioè, il motore di database).

Ogni tabella temporale ha due colonne definite in modo esplicito, ciascuna con un tipo di dati datetime2 . Queste colonne sono chiamate colonne d'epoca . Il motore di database utilizza esclusivamente queste colonne di periodo per registrare il periodo di validità di ogni riga ogni volta che una riga viene modificata. La tabella principale che memorizza i dati attuali è chiamata tabella corrente, o tabella temporale.

Oltre a queste colonne del periodo, una tabella temporale contiene anche un riferimento a un'altra tabella con schema speculare, chiamata 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 fonti di dati reali sono dinamiche e le decisioni aziendali spesso si basano su intuizioni che gli analisti ottengono 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 passato
  • 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 Temporal?

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 ciascuna di queste tabelle, due colonne aggiuntive datetime2 definiscono 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 della ValidFrom colonna all'ora di inizio della transazione corrente (nel fuso orario UTC) basandosi sull'orologio del sistema e assegna il valore della ValidTo colonna al valore massimo di 9999-12-31. In questo modo la riga viene contrassegnata come aperta.

  • Aggiornamenti: Il sistema memorizza il valore precedente della riga nella tabella storica e imposta il valore della ValidTo colonna all'ora di inizio della transazione corrente (nel fuso orario UTC) in base all'orologio 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.

  • Eliminazioni: Il sistema memorizza il valore precedente della riga nella tabella di cronologia e imposta il valore per la colonna ValidTo all'istante di inizio della transazione corrente (nel fuso orario UTC) in base all'orologio 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.

  • Merge: L'operazione si comporta esattamente come se venisse eseguita fino a tre istruzioni (an INSERT, an UPDATE, e/o a DELETE), a seconda di ciò che viene specificato come azioni 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 tramite join multipli e tramite viste basate su più tabelle temporali.

Quando si utilizza la FOR SYSTEM_TIME clausola con una delle cinque sottoclausole in una query, i risultati includono dati storici dalla tabella temporale, come mostrato nell'immagine seguente.

Diagramma che mostra come funziona l'interrogazione temporale.

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).

Il motore di database genera quelle righe se esegui più aggiornamenti sulla stessa chiave primaria all'interno della stessa transazione. In tale caso, le query temporali restituiscono solo le versioni delle righe precedenti alle transazioni e le righe correnti successive alle 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 clausola FROM più JOIN, APPLY, PIVOT e Eseguire query sui dati in una tabella temporale con controllo delle versioni gestito dal sistema.

Expression Righe Qualificanti Note
AS OF date_time ValidFrom <= date_timeAND 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 data_ora_inizioTOdata_ora_fine ValidFrom < data_ora_fineAND ValidTo >data_ora_inizio 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 data_ora_inizioANDdata_ora_fine ValidFrom <= data_ora_fineAND ValidTo >data_ora_inizio Come sopra nella descrizione FOR SYSTEM_TIME FROMstart_date_timeTOend_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 >= data_ora_inizioAND ValidTo <=data_ora_fine 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

Puoi nascondere le colonne del periodo, in modo che le query SELECT che non vi fanno esplicito riferimento non restituiscano queste colonne, come 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 continuano a funzionare come se queste nuove colonne del periodo non fossero presenti (e i valori delle colonne vengono compilati automaticamente).

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

Samples

Contenuti correlati

  • Last updated on