Query sui dati in una tabella temporale con controllo delle versioni di sistemaQuerying Data in a System-Versioned Temporal Table

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

Quando occorre recuperare lo stato (effettivo) più aggiornato dei dati in una tabella temporale, è possibile eseguire query esattamente nello stesso modo valido per l'esecuzione di query in tabelle non temporali.When you want to get latest (actual) state of data in a temporal table, you can query completely the same way as you query non-temporal table. Se le colonne PERIOD non sono nascoste, i rispettivi valori compariranno in una query SELECT * .If the PERIOD columns are not hidden, their values will appear in a SELECT * query. Se le colonne PERIOD sono state specificate come nascoste, i rispettivi valori non saranno visualizzati in una query SELECT * .If you specified PERIOD columns as hidden, their values won’t appear in a SELECT * query. Quando le colonne PERIOD sono nascoste, è possibile fare riferimento in modo specifico alle colonne PERIOD nella clausola SELECT per restituire i valori per queste colonne.When the PERIOD columns are hidden, reference the PERIOD columns specifically in the SELECT clause to return the values for these columns.

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.To perform any type of time-based analysis, use the new FOR SYSTEM_TIME clause with four temporal-specific sub-clauses to query data across the current and history tables. Per ulteriori informazioni su queste clausole, vedere Tabelle temporali e FROM (Transact-SQL)For more information on these clauses, see Temporal Tables and FROM (Transact-SQL)

  • AS OF <data_ora>AS OF <date_time>

  • FROM <data_ora_inizio> TO <data_ora_fine>FROM <start_date_time> TO <end_date_time>

  • BETWEEN <data_ora_inizio> AND <data_ora_fine>BETWEEN <start_date_time> AND <end_date_time>

  • CONTAINED IN (<data_ora_inizio> , <data_ora_fine>)CONTAINED IN (<start_date_time> , <end_date_time>)

  • ALLALL

    È possibile specificareFOR SYSTEM_TIME in modo indipendente per ogni tabella in una query.FOR SYSTEM_TIME can be specified independently for each table in a query. La clausola può essere usata all'interno di espressioni di tabella comuni, funzioni con valore di tabella e stored procedure.It can be used inside common table expressions, table-valued functions and stored procedures.

Query per un data/ora specifica con la sottoclausola AS-OFQuery for a specific time using the AS OF sub-clause

Usare la sottoclausolaAS OF quando è necessario ricostruire lo stato dei dati esistente in un momento specifico nel passato.Use theAS OF sub-clause when you need to reconstruct state of data as it was at any specific time in the past. È possibile ricostruire i dati con la precisione del tipo datetime2 specificato nelle definizioni di colonna PERIOD .You can reconstruct the data with the precision of datetime2 type that was specified in PERIOD column definitions.
La sottoclausolaAS OF può essere usata con valori letterali costanti o variabili e ciò consente di specificare in modo dinamico la condizione temporale.TheAS OF sub-clause clause can be used with constant literals or with variables, which allows you to dynamically specify time condition. I valori specificati vengono interpretati come ora UTC.The values provided values are interpreted as UTC time.

Questo primo esempio restituisce lo stato della tabella dbo.Department in riferimento (AS OF) a una data specifica nel passato.This first example returns the state of the dbo.Department table AS OF a specific date in the past.

/*State of entire table AS OF specific date in the past*/   
SELECT [DeptID], [DeptName], [SysStartTime],[SysEndTime]   
FROM [dbo].[Department]   
FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011' ;  

Questo secondo esempio confronta i valori tra due punti nel tempo per un subset di righe.This second example compares the values between two points in time for a subset of rows.

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.[SysStartTime], D.[SysStartTime],   
D_1_Ago.[SysEndTime], D.[SysEndTime]   
FROM [dbo].[Department] FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago   
JOIN [Department] AS D ON  D_1_Ago.[DeptID] = [D].[DeptID]    
AND D_1_Ago.[DeptID] BETWEEN 1 and 5 ;  

Uso delle viste con la sottoclausola AS OF nelle query temporaliUsing views with AS-OF sub-clause in temporal queries

Usare le viste è molto utile in scenari che richiedono analisi temporizzate complesse.Using views is very useful in scenarios when complex point-in time analysis is required.
Un esempio comune è la generazione di un report aziendale con i valori per il mese precedente.A common example is generating a business report today with the values for previous month.
I clienti si affidano in genere a un modello di database normalizzato che include molte tabelle con relazioni di chiave esterna.Usually, customers have a normalized database model which involves many tables with foreign key relationships. 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.Answering the question how data from that normalized model looked like at a point in the past can very challenging, since all tables change independently, on their own cadence.
In questo caso, la soluzione migliore consiste nel creare una vista e applicare la sottoclausola AS OF all'intera vista.In this case, the best option is to create a view and apply the AS OF sub-clause to the entire view. Questo approccio consente di separare la modellazione del livello di accesso ai dati dall'analisi temporizzata, perché SQL Server applicherà la clausola AS OF in modo trasparente a tutte le tabelle temporali che fanno parte della definizione della vista.Using this approach allows you to decouple modeling of the data access layer from point-in time analysis as SQL Server will apply AS OF clause transparently to all temporal tables that participate in view definition. Inoltre, è possibile combinare tabelle temporali e non temporali nella stessa vista e la clausola AS OF verrà applicata solo a quelle temporali.Furthermore, you can combine temporal with non-temporal tables in the same view and AS OF will be applied only to temporal ones. Se la vista non fa riferimento ad almeno una tabella temporale, l'applicazione di clausole di query temporali avrà esito negativo con un errore.If view does not reference at least one temporal table, applying temporal querying clauses to it will fail with an error.

/* Create view that joins three temporal tables: Department, CompanyLocation, 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   
/* Querying view AS OF */   
SELECT * FROM [vw_GetOrgChart]   
FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011' ;  

Query per le modifiche apportate a colonne specifiche nel tempoQuery for changes to specific rows over time

Le sottoclausole temporali FROM...TO, BETWEEN...AND e CONTAINED IN sono utili per l'esecuzione di un controllo dei dati, ovvero quando è necessario recuperare tutte le modifiche cronologiche per una riga specifica nella tabella corrente.The temporal sub-clauses FROM...TO, BETWEEN...AND and CONTAINED IN are useful when you want to perform a data audit, i.e. when you need to get all historical changes for a specific row in the current table.
Le prime due sottoclausole restituiscono le versioni di riga che si sovrappongono a un periodo specificato (ad esempio 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.The first two sub-clauses return row versions that overlap with a specified period (i.e. those that started before given period and ended after it), while CONTAINED IN returns only those that existed within specified period boundaries.

Importante

Se è necessario cercare solo le versioni di riga non aggiornate, è consigliabile usare CONTAINED IN , perché opera solo sulla tabella di cronologia e offre le migliori prestazioni di query.If you search for non-current row versions only, we recommend you to use CONTAINED IN as it works only with the history table and will yield the best query performance. Usare ALL quando occorre eseguire query sui dati correnti e cronologici senza restrizioni.Use ALL when you need to query current and historical data without any restrictions.

/* Query using BETWEEN...AND sub-clause*/  
SELECT   
     [DeptID]  
   , [DeptName]  
   , [SysStartTime]  
   , [SysEndTime]  
   , IIF (YEAR(SysEndTime) = 9999, 1, 0) AS IsActual   
FROM [dbo].[Department]   
FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31'   
WHERE DeptId = 1   
ORDER BY SysStartTime DESC;   

/*  Query using CONTAINED IN sub-clause */  
SELECT [DeptID], [DeptName], [SysStartTime],[SysEndTime]   
FROM [dbo].[Department]   
FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   
WHERE DeptId = 1   
ORDER BY SysStartTime DESC ;  

/*  Query using ALL sub-clause */   
SELECT    
     [DeptID]   
   , [DeptName]   
   , [SysStartTime]   
   , [SysEndTime]   
   , IIF (YEAR(SysEndTime) = 9999, 1, 0) AS IsActual    
FROM [dbo].[Department] FOR SYSTEM_TIME ALL   
ORDER BY [DeptID], [SysStartTime] Desc  

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

Tabelle temporali Temporal Tables
FROM (Transact-SQL) FROM (Transact-SQL)
Creazione di una tabella temporale con controllo delle versioni di sistema Creating a System-Versioned Temporal Table
Modifica dei dati in una tabella temporale con controllo delle versioni di sistema Modifying Data in a System-Versioned Temporal Table
Modifica dello schema di una tabella temporale con controllo delle versioni di sistema Changing the Schema of a System-Versioned Temporal Table
Arresto del controllo delle versioni di sistema in una tabella temporale con controllo delle versioni di sistemaStopping System-Versioning on a System-Versioned Temporal Table