Come utilizzare la tabella temporale SQL Server

Le tabelle temporali, note anche come tabelle versioni di sistema, sono tabelle che consentono di tracciare e mantenere una cronologia dei dati su una tabella specifica. Utilizzando le tabelle temporali, è possibile tenere traccia della cronologia delle modifiche apportate ai dati in una tabella.

Questo articolo presenterà le basi per creare, lavorare e utilizzare le tabelle temporali in SQL Server.

Le tabelle di sistema di sistema sono state introdotte nello standard ANSI SQL 2011 e sono state disponibili come funzionalità in SQL Server 2016 e superiore.

A differenza di una tabella normale che può solo mostrare e funzionare con i dati attuali, le tabelle temporali consentono di visualizzare e lavorare anche con dati precedentemente eliminati. Come accennato, ciò è possibile a causa della capacità di una tabella temporale di tenere traccia delle modifiche apportate ai dati in una tabella.

La tabella contiene due colonne chiave: SyssstartTime e SysendTime. Queste due colonne vengono utilizzate per definire i dati esistenti e precedenti per ogni record in una tabella. È possibile utilizzare intervalli di tempo specifici per visualizzare come sono cambiati i dati in una tabella.

Crea una tabella temporale

Prima di poter creare una tabella temporale, deve soddisfare i seguenti requisiti:

1. Una tabella temporale deve contenere un vincolo chiave primario definito.
2. Deve contenere due colonne per registrare la data di avvio e fine. Queste colonne devono essere del tipo di dati DateTime2. Le colonne devono essere dichiarate generate sempre come avvio/fine riga.
3. SQL Server presuppone che le due colonne non siano nulli. Pertanto, l'istruzione Crea Tabella non riesce se la query cerca di impostare colonne che sono nulli.
4. SQL Server genera automaticamente una tabella di cronologia utilizzando uno schema simile alla tabella temporale.
5. Non è possibile utilizzare al posto dei trigger in una tabella versione del sistema.
6. La tabella della cronologia non dovrebbe contenere alcun vincolo.
7. Non è possibile modificare i dati nella tabella della cronologia.
8. Le dichiarazioni, come Insert and Update, non possono fare riferimento alle colonne del periodo.
9. La tabella della cronologia viene creata come tabella di cronologia delle righe e la compressione della pagina viene applicata se applicabile. Altrimenti, il tavolo viene lasciato non compresso.
10. SQL Server genererà automaticamente un indice cluster per la tabella della cronologia.

Come creare una tabella temporale: T-SQL

Diamo un'occhiata a una semplice dimostrazione di creare una tabella temporale. Considera la query di esempio mostrata di seguito:

Crea la tabella DBO.my_temporal_table (
id int,
fname varchar (50),
Email Varchar (255),
Dipartimento Varchar (50),
Tasto primario PK vincolante (ID),
Syssstarttime datetime2 generato sempre come la riga inizia non null,
Sysendtime datetime2 generato sempre come riga non null,
periodo per system_time (syssstarttime, sysendtime)) con (system_versioning = on);

Una volta eseguita la query sopra, il server SQL creerà la tabella con il nome specificato.

In SQL Server Management Studio, è possibile visualizzare una tabella di versione di sistema espandendo l'opzione tabelle nel database di destinazione:

Si noti che il server SQL genera automaticamente una tabella di cronologia con uno schema simile alla tabella versione del sistema. Tuttavia, presta attenzione alle colonne nella tabella della cronologia. Si noti che non hanno alcun vincolo.

Considera l'immagine mostrata di seguito:

Come vedrai, SQL Server genera una tabella di cronologia con un nome che segue un formato specifico. Per impostare un nome personalizzato per la tabella cronologia, specificarlo nell'istruzione Crea tabella come mostrato:

---
periodo per system_time (syssstarttime, sysendtime)) con (system_versioning = on, history_table = mytemporal_tablehistory);
---

Successivamente, se si espande l'opzione degli indici per la tabella della cronologia, si noti che SQL Server generato automaticamente un indice cluster:

Usando tabelle temporali

Proviamo la funzionalità delle tabelle temporali inserendo alcuni record nella tabella. Considera la query di esempio mostrata di seguito:

Inserisci in my_temporal_table (id, fname, e -mail, reparto)
Valori (1, "John Davis", "[email protected] ',' front-end '),
(2, "Ruby Raw", "[email protected] ',' database '),
(3, "Scott Turner", "[email protected] ',' full-stack '),
(4, "Alice Jensen", "[email protected] ',' Versione Control '),
(5, "Peter Green", '[email protected] ',' backend ');

Una volta inseriti i dati di esempio nella tabella, possiamo interrogarlo come:

Seleziona * da my_temporal_table;

Dovresti ottenere un output vicino a quello mostrato di seguito come

Per capire come funziona la tabella versione del sistema, eliminiamo e aggiorniamo le righe nella tabella:

Elimina da my_temporal_table dove dipartimento = 'database';
Aggiorna my_temporal_table set fname = 'john m' dove id = 5;

Successivamente, interrogare i dati nella tabella principale:

Seleziona * da my_temporal_table;

Se interroghi la tabella della cronologia, dovresti vedere la vecchia versione dei dati con i timestamp corretti.

Conclusione

Questa guida ha coperto il concetto di tabella versione temporale o di sistema in SQL Server. Utilizzando questa guida, sarai in grado di tracciare la cronologia dei dati utilizzando le tabelle temporali SQL Server. Speriamo che tu abbia trovato questo articolo utile. Dai un'occhiata a altri articoli di suggerimento Linux per suggerimenti e tutorial.