Tabelle temporaliTemporal Tables

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In SQL Server 2016 è stato introdotto il supporto per le tabelle temporali con controllo delle versioni del sistema come una funzionalità di database, che offre un supporto predefinito per la gestione delle informazioni sui dati archiviati nella tabella in qualsiasi momento anziché solo sui dati che risultano corretti nel momento attuale.SQL Server 2016 introduced support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Questa funzionalità di database è stata introdotta in SQL ANSI 2011.Temporal is a database feature that was introduced in ANSI SQL 2011.

Avvio rapidoQuick Start

Che cos'è una tabella temporale con controllo delle versioni di sistema?What is a system-versioned temporal table?

Una tabella temporale con controllo delle versioni di sistema è un tipo di tabella utente progettato per mantenere una cronologia completa delle modifiche dei dati e semplificare l'analisi temporizzata.A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Questo tipo di tabella temporale è definito tabella temporale con controllo delle versioni di sistema perché il periodo di validità per ogni riga viene gestito dal sistema, ad esempio, il motore di database.This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

Ogni tabella temporale ha due colonne definite in modo esplicito, ciascuna con un tipo di dati datetime2 .Every temporal table has two explicitly defined columns, each with a datetime2 data type. Queste colonne sono note come colonne periodo.These columns are referred to as period columns. Le colonne periodo vengono usate esclusivamente dal sistema per registrare il periodo di validità per ciascuna riga ogni volta che una riga viene modificata.These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

Oltre alle colonne periodo, una tabella temporale contiene anche un riferimento a un'altra tabella con schema con mirroring.In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. Il sistema usa questa tabella per archiviare automaticamente la versione precedente della riga ogni volta che una riga della tabella temporale viene aggiornata o eliminata.The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Questa tabella aggiuntiva è detta tabella di cronologia, mentre la tabella principale che contiene le versioni attuali (effettive) delle righe è definita tabella corrente o semplicemente tabella temporale.This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. Durante la creazione di una tabella temporale gli utenti possono specificare una tabella di cronologia esistente (deve essere conforme allo schema) oppure consentire al sistema di creare una tabella di cronologia predefinita.During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

Perché temporale?Why temporal?

Le origini dati reali sono dinamiche e quasi sempre le decisioni aziendali si basano su approfondimenti che gli analisti ricavano dall'evoluzione dei dati.Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Alcuni casi d'uso delle tabelle temporali:Use cases for temporal tables include:

  • Controllo di tutte le modifiche dei dati ed esecuzione di analisi forensi, se necessarioAuditing all data changes and performing data forensics when necessary

  • Ricostruzione dello stato dei dati in qualsiasi momento trascorsoReconstructing state of the data as of any time in the past

  • Calcolo delle tendenze nel tempoCalculating trends over time

  • Gestione di una dimensione a modifica lenta per le applicazioni di supporto decisionaleMaintaining a slowly changing dimension for decision support applications

  • Recupero da modifiche accidentali dei dati ed errori delle applicazioniRecovering from accidental data changes and application errors

Come funziona una tabella temporale?How does temporal work?

Il controllo delle versioni di sistema per una tabella viene implementato come una coppia di tabelle, una tabella corrente e una tabella di cronologia.System-versioning for a table is implemented as a pair of tables, a current table and a history table. All'interno di ogni tabella vengono usate due colonne datetime2 aggiuntive per definire il periodo di validità per ogni riga:Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Colonna di inizio periodo: il sistema registra l'ora di inizio per la riga in questa colonna, in genere indicata come colonna SysStartTime .Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.

  • Colonna di fine periodo: il sistema registra l'ora di fine per la riga in questa colonna, in genere indicata come colonna SysEndTime .Period end column: The system records the end time for the row in this column, typically denoted at the SysEndTime column.

    La tabella corrente contiene il valore corrente per ogni riga.The current table contains the current value for each row. La tabella di cronologia contiene ogni valore precedente per ogni riga, se presente, e l'ora di inizio e di fine del relativo periodo di validità.The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

    Temporal-HowWorksTemporal-HowWorks

    L'esempio seguente illustra uno scenario con informazioni su Employee in un ipotetico database delle risorse umane:The following simple example illustrates a scenario with Employee information in hypothetical HR database:

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

INSERIMENTI: su INSERTil sistema imposta il valore per la colonna SysStartTime sull'ora di inizio della transazione corrente (fuso orario UTC) in base al clock di sistema e assegna come valore per la colonna SysEndTime il valore massimo di 31-12-9999.INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. In questo modo la riga viene contrassegnata come aperta.This marks the row as open.

AGGIORNAMENTI: su UPDATEil sistema archivia il valore precedente della riga nella tabella di cronologia e imposta il valore per la colonna SysEndTime sull'ora di inizio della transazione corrente (fuso orario UTC) in base al clock di sistema.UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. In questo modo la riga viene contrassegnata come chiusa, con un periodo registrato in cui risultava valida.This marks the row as closed, with a period recorded for which the row was valid. Nella tabella corrente la riga viene aggiornata con il nuovo valore e il sistema imposta il valore per la colonna SysStartTime sull'ora di inizio della transazione (fuso orario UTC) in base al clock di sistema.In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. Il valore per la riga aggiornata nella tabella corrente per la colonna SysEndTime rimane il valore massimo di 31-12-9999.The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.

ELIMINAZIONI: su DELETEil sistema archivia il valore precedente della riga nella tabella di cronologia e imposta il valore per la colonna SysEndTime sull'ora di inizio della transazione corrente (fuso orario UTC) in base al clock di sistema.DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. In questo modo la riga viene contrassegnata come chiusa, con un periodo registrato in cui la riga precedente risultava valida.This marks the row as closed, with a period recorded for which the previous row was valid. Nella tabella corrente la riga viene rimossa.In the current table, the row is removed. Le query della tabella corrente non restituiscono questa riga.Queries of the current table will not return this row. Solo le query che gestiscono i dati di cronologia restituiscono dati per i quali viene chiusa una riga.Only queries that deal with history data return data for which a row is closed.

UNIONE: su MERGEl'operazione si comporta esattamente come se fossero eseguite fino a tre istruzioni ( INSERT, UPDATEe/o DELETE), in base alle azioni specificate nell'istruzione MERGE .MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

Importante

I tempi registrati nelle colonne datetime2 del sistema sono basati sull'ora di inizio della transazione stessa.The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. Ad esempio, tutte le righe inserite all'interno di una singola transazione avranno la stessa ora UTC registrata nella colonna corrispondente all'inizio del periodo SYSTEM_TIME .For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Come si esegue una query sui dati temporali?How do I query temporal data?

La clausola FROM<tabella> dell'istruzione SELECT usa 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.The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. La nuova sintassi dell'istruzione SELECT è supportata direttamente su una singola tabella, propagata attraverso diversi join e viste su più tabelle temporali.This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Temporal-QueryingTemporal-Querying

La query seguente esegue la ricerca di versioni delle righe della riga Employee con EmployeeID = 1000 attive almeno per una parte del periodo compreso tra il 1° gennaio 2014 e il 1° gennaio 2015, incluso il limite superiore:The following query searches for row versions for Employee row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

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

Nota

FOR SYSTEM_TIME esclude le righe che hanno un periodo di validità con durata pari a zero (SysStartTime = SysEndTime).FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime).
Tali righe verranno generate se si eseguono più aggiornamenti sulla stessa chiave primaria nell'ambito della stessa transazione.Those rows will be generated if you perform multiple updates on the same primary key within the same transaction.
In questo caso, la query temporale rileva solo le versioni di riga precedenti alle transazioni e quelle che sono diventate effettive dopo le transazioni.In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions.
Se è necessario includere le righe nell'analisi, eseguire la query direttamente nella tabella di cronologia.If you need to include those rows in the analysis, query the history table directly.

Nella tabella seguente il valore SysStartTime della colonna delle righe risultanti rappresenta il valore presente nella colonna SysStartTime della tabella su cui si esegue la query e SysEndTime rappresenta il valore presente nella colonna SysEndTime della tabella su cui si esegue la query.In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. Per la sintassi completa e per esempi, vedere FROM (Transact-SQL) e Query sui dati in una tabella temporale con controllo delle versioni di sistema.For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

EspressioneExpression Righe risultantiQualifying Rows DescrizioneDescription
AS OF<date_time>AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time Restituisce una tabella con una singola riga contenente i valori che erano effettivi (correnti) in un momento specificato nel passato.Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia e i risultati vengono filtrati in modo da restituire i valori nella riga che era valida nel momento specificato dal parametro <date_time>.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. Il valore per 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>.The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.
FROM<start_date_time>TO<end_date_time>FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_timeSysStartTime < end_date_time AND SysEndTime > 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 a essere attive prima del valore del parametro <start_date_time> per l'argomento FROM o non siano più state attive dopo il valore del parametro <end_date_time> per l'argomento TO.Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. Internamente, viene eseguita un'unione tra la tabella temporale e la relativa tabella di cronologia e 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.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. 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.Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time Come sopra per la descrizione di FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time>, tranne per il fatto che la tabella delle righe restituite include le righe diventate attive in corrispondenza del limite superiore definito dall'endpoint <end_date_time>.Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= 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 datetime per l'argomento CONTAINED IN.Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. 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.Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALLALL Tutte le righeAll rows Restituisce l'unione di righe che appartengono alla tabella corrente e a quella di cronologia.Returns the union of rows that belong to the current and the history table.

Nota

Facoltativamente, è possibile scegliere di nascondere le colonne periodo in modo tale che le query che non fanno riferimento in modo esplicito alle colonne periodo non le restituiscano (scenario SELECT * FROM<tabella>).Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT * FROM<table> scenario). Per restituire una colonna nascosta, basta fare riferimento in modo esplicito alla colonna nella query.To return a hidden column, simply explicitly refer to the hidden column in the 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 saranno popolati automaticamente).Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). Per altre informazioni sull'uso della clausola HIDDEN , vedere CREATE TABLE (Transact-SQL) e ALTER TABLE (Transact-SQL).For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

Questo articolo è stato utile?Did this Article Help You? Commenti e suggerimentiWe’re Listening

Quali informazioni si stanno cercando? La ricerca ha restituito i risultati desiderati?What information are you looking for, and did you find it? Microsoft incoraggia gli utenti a inviare i propri commenti per migliorare i contenutiWe’re listening to your feedback to improve the content. Inviare eventuali commenti all'indirizzo sqlfeedback@microsoft.comPlease submit your comments to sqlfeedback@microsoft.com

Vedere ancheSee Also

Introduzione alle tabelle temporali con controllo delle versioni di sistema Getting Started with System-Versioned Temporal Tables
Tabelle temporali con controllo delle versioni di sistema con tabelle con ottimizzazione per la memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Scenari di utilizzo delle tabelle temporali Temporal Table Usage Scenarios
Considerazioni e limitazioni delle tabelle temporali Temporal Table Considerations and Limitations
Gestire la conservazione dei dati cronologici nelle tabelle temporali con controllo delle versioni di sistema Manage Retention of Historical Data in System-Versioned Temporal Tables
Partizionamento con le tabelle temporali Partitioning with Temporal Tables
Verifiche di coerenza del sistema della tabella temporale Temporal Table System Consistency Checks
Sicurezza di una tabella temporale Temporal Table Security
Funzioni e viste per i metadati delle tabelle temporaliTemporal Table Metadata Views and Functions