Scenari di utilizzo delle tabelle temporaliTemporal Table Usage Scenarios

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

Le tabelle temporali sono in genere utili negli scenari che richiedono il rilevamento della cronologia delle modifiche dei dati.Temporal Tables are generally useful in scenarios that require tracking history of data changes.
È consigliabile prendere in considerazione le tabelle temporali nei casi d'uso seguenti per ottenere vantaggi significativi in termini di produttività.We recommend you to consider Temporal Tables in the following use cases for major productivity benefits.

Controllo dei datiData Audit

Usare il controllo delle versioni di sistema temporale nelle tabelle che archiviano informazioni critiche per le quali è necessario tenere traccia di cosa è stato modificato e quando ed eseguire analisi scientifiche dei dati in qualsiasi momento.Use temporal system-versioning on tables that store critical information for which you need to keep track of what has changed and when, and to perform data forensics at any point in time.
Le tabelle temporali con controllo delle versioni di sistema consentono di pianificare scenari di controllo dei dati nelle prime fasi del ciclo di sviluppo o di aggiungere il controllo dei dati alle applicazioni o soluzioni esistenti quando necessario.Temporal system-versioned tables allows you to plan for data audit scenarios in the early stages of the development cycle or to add data auditing to existing applications or solutions when you need it.

Il diagramma seguente mostra lo scenario di una tabella Employee con il campione di dati che include versioni di riga correnti, contrassegnate dal colore blu, e versioni di riga cronologiche, contrassegnate dal colore grigio.The following diagram shows an Employee table scenario with the data sample including current (marked with blue color) and historical row versions (marked with grey color).
La parte destra del diagramma visualizza le versioni di riga sull'asse temporale e quali sono le righe selezionate con diversi tipi di query sulla tabella temporale con o. senza la clausola SYSTEM_TIME.The right-hand portion of the diagram visualizes row versions on time axis and what are the rows you select with different types of querying on temporal table with or without SYSTEM_TIME clause.

TemporalUsageScenario1TemporalUsageScenario1

Abilitazione del controllo delle versioni di sistema in una nuova tabella per il controllo dei datiEnabling system-versioning on a new table for data audit

Se sono state identificate le informazioni che richiedono il controllo dei dati, creare tabelle di database come tabelle temporali con controllo delle versioni di sistema.If you have identified information that needs data audit, create database tables as temporal system-versioned. 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 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));  

In Creating a System-Versioned Temporal Table(Creazione di una tabella temporale con controllo delle versioni di sistema) sono descritte varie opzioni per creare una tabella temporale con controllo delle versioni di sistema.Various options to create temporal system-versioned table are described in Creating a System-Versioned Temporal Table.

Abilitazione del controllo delle versioni di sistema in una tabella esistente per il controllo dei datiEnabling system-versioning on an existing table for data audit

Se è necessario eseguire il controllo dei dati nei database esistenti, usare ALTER TABLE per estendere le tabelle non temporali in modo che diventino tabelle con controllo delle versioni di sistema.If you need to perform data audit in existing databases, use ALTER TABLE to extend non-temporal tables to become system-versioned. Per evitare modifiche di rilievo nell'applicazione, aggiungere le colonne del periodo come HIDDEN, come descritto in Alter Non-Temporal Table to be System-Versioned Temporal Table(Modifica di una tabella non temporale in tabella temporale con controllo delle versioni di sistema).In order to avoid breaking changes in your application, add period columns as HIDDEN, as explained in Alter Non-Temporal Table to be System-Versioned Temporal Table. L'esempio seguente illustra l'abilitazione del controllo delle versioni di sistema in una tabella Employee esistente in un ipotetico database delle risorse umane:The following example illustrates enabling system-versioning on an existing Employee table in a hypothetical HR database:

/*   
Turn ON system versioning in Employee table in two steps   
(1) add new period columns (HIDDEN)   
(2) create default history table   
*/   
ALTER TABLE Employee   
ADD   
    ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , ValidTo datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);   

ALTER TABLE Employee    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));  

Dopo avere eseguito lo script precedente, tutte le modifiche dei dati verranno raccolte in modo trasparente nella tabella di cronologia.After executing the above script, all data changes will be collected transparently in the history table.
In un tipico scenario di controllo dei dati si eseguono query per tutte le modifiche dei dati applicate a una singola riga in un periodo di tempo di interesse.In typical data audit scenario, you would query for all data changes that were applied to an individual row within a period of time of interest. La tabella di cronologia predefinita viene creata con albero B con rowstore cluster, per risolvere in modo efficiente questo caso d'uso.The default history table is created with clustered row-store B-Tree to efficiently address this use case.

Esecuzione di analisi dei datiPerforming data analysis

Dopo l'abilitazione del controllo delle versioni di sistema con uno dei metodi precedenti, per il controllo dei dati è sufficiente eseguire una query.After enabling system-versioning using either of the above approaches, data audit is just one query away from you. La query seguente esegue la ricerca di versioni delle righe del record 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 record 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;  

Sostituire FOR SYSTEM_TIME BETWEEN...AND con FOR SYSTEM_TIME ALL per analizzare l'intera cronologia delle modifiche dei dati per il dipendente specifico:Replace FOR SYSTEM_TIME BETWEEN...AND with FOR SYSTEM_TIME ALL to analyze the entire history of data changes for that particular employee:

SELECT * FROM Employee   
    FOR SYSTEM_TIME ALL WHERE    
        EmployeeID = 1000 ORDER BY ValidFrom;  

Per cercare le versioni delle righe attive solo all'interno di un periodo, e non al di fuori, usare CONTAINED IN.To search for row versions that were active only within a period (and not outside of it), use CONTAINED IN. Questa query è molto efficiente perché viene eseguita solo sulla tabella di cronologia:This query is very efficient because it only queries the history table:

SELECT * FROM Employee FOR SYSTEM_TIME    
    CONTAINED IN ('2014-01-01 00:00:00.0000000', '2015-01-01 00:00:00.0000000')   
        WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Infine, in alcuni scenari di controllo è possibile vedere l'aspetto che aveva l'intera tabella in un momento qualsiasi nel passato:Finally, in some audit scenarios, you may want to see how entire table looked like at any point in time in the past:

SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2014-01-01 00:00:00.0000000' ;  

Le tabelle temporali con controllo delle versioni di sistema archiviano i valori per le colonne del periodo con il fuso orario UTC, mentre è sempre più pratico utilizzare il fuso orario locale per filtrare i dati e visualizzare i risultati.System-versioned temporal tables store values for period columns in UTC time zone, while it is always more convenient to work with local time zone both for filtering data and displaying results. L'esempio di codice seguente illustra come applicare una condizione di filtro specificata in origine nel fuso orario locale e quindi convertita nell'ora UTC con AT TIME ZONE introdotta in SQL Server 2016:The following code example shows how to apply filtering condition that is originally specified in the local time zone and then converted to UTC using AT TIME ZONE introduced in SQL Server 2016:

/*Add offset of the local time zone to current time*/  
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time'  
/*Convert AS OF filter to UTC*/  
SET @asOf = DATEADD (MONTH, -9, @asOf) AT TIME ZONE 'UTC';  

SELECT   
    EmployeeID  
    , Name  
    , Position  
    , Department  
    , [Address]  
    , [AnnualSalary]  
    , ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT   
    , ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT  
FROM Employee   
    FOR SYSTEM_TIME AS OF @asOf where EmployeeId = 1000  

L'uso di AT TIME ZONE è utile in tutti gli altri scenari in cui vengono usate tabelle con controllo delle versioni di sistema.Using AT TIME ZONE is helpful in all other scenarios where system-versioned tables are used.

Suggerimento

Le condizioni del filtro specificate nelle clausole temporali con FOR SYSTEM_TIME hanno requisiti SARG-able (Search ARGument ABLE), ovveroFiltering conditions specified in temporal clauses with FOR SYSTEM_TIME are SARG-able (i.e SQL Server può usare l'indice cluster sottostante per eseguire una ricerca anziché un'operazione di analisi.SQL Server can utilize underlying clustered index to perform a seek instead of a scan operation.
Se si esegue direttamente una query sulla tabella di cronologia, assicurarsi che la condizione di filtro abbia i requisiti SARGable specificando i filtri nel formato <colonna PERIOD> {< | > | =, …}If you query the history table directly, make sure that your filtering condition is also SARG-able by specifying filters in form of <period column> {< | > | =, …} date_condition AT TIME ZONE 'UTC'.date_condition AT TIME ZONE ‘UTC’.
Se si applica AT TIME ZONE alle colonne del periodo, SQL Server esegue un'analisi di tabella/indice, che può risultare molto costosa.If you apply AT TIME ZONE to period columns, SQL Server will perform a table/index scan, which can be very expensive. Per ovviare a questo tipo di condizione nelle query:Avoid this type of condition in your queries:
<colonna PERIOD> AT TIME ZONE '<<fuso orario' > {< | > | =, …}<period column> AT TIME ZONE ‘<your time zone>’ > {< | > | =, …} date_condition.date_condition.

Vedere anche: Querying Data in a System-Versioned Temporal Table(Esecuzione di query sui dati in una tabella temporale con controllo delle versioni di sistema).See also: Querying Data in a System-Versioned Temporal Table.

Analisi temporizzate (spostamento cronologico)Point in Time Analysis (Time Travel)

A differenza del controllo dei dati, in cui lo stato attivo è in genere sulle modifiche apportate ai singoli record, negli scenari di spostamento cronologico gli utenti vogliono visualizzare le modifiche di interi set di dati nel tempo.Unlike data audit, where the focus is typically on changes that occurred to an individual records, in time travel scenarios users want to see how entire data sets changed over time. A volte lo spostamento cronologico include diverse tabelle temporali correlate, ognuna delle quali viene modificata con una frequenza diversa, per cui si vogliono analizzare:Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:

  • Tendenze degli indicatori importanti in dati storici e correnti.Trends for the important indicators in the historical and current data

  • Snapshot esatto di tutti i dati "a partire da" qualsiasi punto nel tempo nel passato, ad esempio ieri, un mese fa e così via.Exact snapshot of the entire data “as of” any point in time in the past (yesterday, a month ago, etc.)

  • Differenze tra due punti nel tempo di interesse, ad esempio un mese fa rispetto a tre mesi fa.Differences in between two point in time of interest (a month ago vs. three months ago, for instance)

    Esistono molti scenari reali che richiedono l'analisi dello spostamento cronologico.There are many real-world scenarios which require time travel analysis. Per illustrare questo scenario di utilizzo, si esaminerà OLTP con la cronologia generata automaticamente.To illustrate this usage scenario, let's look at OLTP with auto-generated history.

OLTP con la cronologia dei dati generata automaticamenteOLTP with Auto-Generated Data History

Nei sistemi di elaborazione delle transazioni non è insolito analizzare l'importanza del cambiamento delle metriche nel tempo.In transaction processing systems, it is not unusual to analyze how important metrics change over time. Idealmente, l'analisi della cronologia non deve compromettere le prestazioni dell'applicazione OLTP in cui l'accesso allo stato più recente dei dati deve verificarsi con latenza e blocco dei dati minimi.Ideally, analyzing history should not compromise performance of the OLTP application where access to the latest state of data must occur with minimal latency and data locking. Le tabelle temporali con controllo delle versioni di sistema sono progettate per consentire agli utenti di mantenere in modo trasparente l'intera cronologia delle modifiche per analisi successive, separatamente dai dati correnti, con un impatto minimo sul carico di lavoro OLTP principale.System-versioned temporal tables are designed to allow users to transparently keep the full history of changes for later analysis, separately from the current data, with the minimal impact on the main OLTP workload.
Per i carichi di lavoro con elaborazione delle transazioni elevata, è consigliabile usare tabelle temporali con controllo delle versioni di sistema con tabelle con ottimizzazione per la memoriache consentono di archiviare i dati correnti in memoria e l'intera cronologia delle modifiche su disco, in modo economicamente conveniente.For high transactional processing workloads, we recommend that you use System-Versioned Temporal Tables with Memory-Optimized Tables, which allow you to store current data in-memory and full history of changes on disk in a cost effective way.

Per la tabella di cronologia è consigliabile usare un indice columnstore cluster per i motivi seguenti:For the history table, we recommend that you use a clustered columnstore index for the following reasons:

  • Le analisi delle tendenze tipiche sfruttano i vantaggi dalle prestazioni di query fornite da un indice columnstore cluster.Typical trend analysis benefits from query performance provided by a clustered columnstore index.

  • L'attività di scaricamento dei dati con tabelle con ottimizzazione per la memoria offre prestazioni migliori con un carico di lavoro OLTP impegnativo, se la tabella di cronologia include un indice columnstore cluster.The data flush task with memory-optimized tables performs best under heavy OLTP workload when the history table has a clustered columnstore index.

  • Un indice columnstore cluster offre un'eccellente compressione, specialmente negli scenari in cui non tutte le colonne vengono modificate contemporaneamente.A clustered columnstore index provides excellent compression, especially in scenarios where not all columns are changed at the same time.

    L'uso di tabelle temporali con OLTP in memoria riduce la necessità di mantenere in memoria l'intero set di dati e consente di distinguere facilmente i dati più usati da quelli usati meno di frequente.Using temporal tables with in-memory OLTP reduces the need to keep the entire data set in-memory and enables you to easily distinguish between hot and cold data.
    La gestione dell'inventario o il trading valutario sono, tra gli altri, esempi di scenari reali che rientrano in questa categoria.Examples of the real-world scenarios that fit well into this category are inventory management or currency trading, among others.

    Il diagramma seguente illustra il modello di dati semplificato usato per la gestione dell'inventario:The following diagram shows simplified data model used for inventory management:

    TemporalUsageInMemoryTemporalUsageInMemory

    L'esempio di codice seguente crea ProductInventory come tabella temporale con controllo delle versioni di sistema in memoria con un indice columnstore cluster nella tabella di cronologia, che in effetti sostituisce l'indice rowstore creato per impostazione predefinita:The following code example creates ProductInventory as an in-memory system-versioned temporal table with a clustered columnstore index on the history table (which actually replaces the row-store index created by default):

Nota

Assicurarsi che il database consenta la creazione di tabelle con ottimizzazione per la memoria.Make sure that your database allows creation of memory-optimized tables. Vedere Creazione di una tabella con ottimizzazione per la memoria e di una stored procedure compilata in modo nativo.See Creating a Memory-Optimized Table and a Natively Compiled Stored Procedure.

USE TemporalProductInventory  
GO  

BEGIN  
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first   
    IF ((SELECT temporal_type FROM SYS.TABLES WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)  
    BEGIN  
        ALTER TABLE [dbo].[ProductInventory] SET (SYSTEM_VERSIONING = OFF)  
    END  
    DROP TABLE IF EXISTS [dbo].[ProductInventory]  
       DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory]  
END  
GO  

CREATE TABLE [dbo].[ProductInventory]  
(  
    ProductId int NOT NULL,  
    LocationID INT NOT NULL,  
    Quantity int NOT NULL CHECK (Quantity >=0),  

    SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START  NOT NULL ,  
    SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END  NOT NULL ,  
    PERIOD FOR SYSTEM_TIME(SysStartTime,SysEndTime),  

    --Primary key definition  
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId)  
)  
WITH  
(  
    MEMORY_OPTIMIZED=ON,      
    SYSTEM_VERSIONING = ON   
    (          
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],          
        DATA_CONSISTENCY_CHECK = ON  
    )  
)  

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory ON [ProductInventoryHistory]  
WITH (DROP_EXISTING = ON);  

Per il modello precedente, ecco come potrebbe apparire la stored procedure per la gestione dell'inventario:For the model above this is how the procedure for maintaining inventory could look like:

CREATE PROCEDURE [dbo].[spUpdateInventory]  
@productId int,  
@locationId int,  
@quantityIncrement int  

WITH NATIVE_COMPILATION, SCHEMABINDING  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English')  
    UPDATE dbo.ProductInventory  
        SET Quantity = Quantity + @quantityIncrement   
            WHERE ProductId = @productId AND LocationId = @locationId  

/*If zero rows were updated than this is insert of the new product for a given location*/  
    IF @@rowcount = 0  
        BEGIN  
            IF @quantityIncrement < 0  
                SET @quantityIncrement = 0  
            INSERT INTO [dbo].[ProductInventory]  
                (  
                    [ProductId]  
                    ,[LocationID]  
                    ,[Quantity]  
                )  
                VALUES  
                   (  
                        @productId  
                       ,@locationId  
                       ,@quantityIncrement  
        END  
END;  

La stored procedure spUpdateInventory inserisce un nuovo prodotto nell'inventario o aggiorna la quantità del prodotto per l'ubicazione specifica.The spUpdateInventory stored procedure either inserts a new product in the inventory or updates the product quantity for the particular location. La logica di business è molto semplice e incentrata sul mantenimento continuo e accurato dello stato più recente mediante l'incremento o il decremento del campo Quantity attraverso l'aggiornamento della tabella, mentre le tabelle con controllo delle versioni di sistema aggiungono in modo trasparente le dimensioni della cronologia ai dati, come illustrato nel diagramma seguente:The business logic is very simple and focused on maintaining the latest state accurate all the time by incrementing / decrementing the Quantity field through table update, while system-versioned tables transparently add history dimension to the data, as depicted on the diagram below

TemporalUsageInMemory2bTemporalUsageInMemory2b

A questo punto, la query per ottenere lo stato più recente può essere eseguita in modo efficiente dal modulo compilato in modo nativo:Now, querying of the latest state can be performed efficiently from the natively compiled module:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]  
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'English')  
    SELECT ProductId, LocationID, Quantity, SysStartTime  
      FROM dbo.ProductInventory  
    ORDER BY ProductId, LocationId  
END;  
GO  
EXEC [dbo].[spQueryInventoryLatestState];  

L'analisi delle modifiche dei dati nel corso del tempo diventa estremamente semplice con la clausola FOR SYSTEM_TIME ALL, come illustrato nell'esempio seguente:Analyzing data changes over time becomes extremely easy with the FOR SYSTEM_TIME ALL clause, as shown in the following example:

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;  
GO  
CREATE VIEW vw_GetProductInventoryHistory  
AS  
   SELECT ProductId, LocationId, Quantity, SysStartTime, SysEndTime   
   FROM [dbo].[ProductInventory]  
   FOR SYSTEM_TIME ALL;  
GO  
SELECT * FROM vw_GetProductInventoryHistory   
    WHERE ProductId = 2;  

Il diagramma seguente mostra la cronologia dei dati per un prodotto, che può essere riprodotta facilmente importando la vista precedente in Power Query, Power BI o uno strumento di Business Intelligence simile:The diagram below shows the data history for one product which can be easily rendered importing the view above in the Power Query, Power BI or similar business intelligence tool:

ProductHistoryOverTimeProductHistoryOverTime

Le tabelle temporali possono essere usate in questo scenario per eseguire altri tipi di analisi di spostamento cronologico, ad esempio la ricostruzione dello stato dell'inventario AS OF qualsiasi punto nel tempo nel passato o il confronto di snapshot appartenenti a momenti diversi.Temporal tables can be used in this scenario to perform other types of time travel analysis, such as reconstructing the state of the inventory AS OF any point in time in the past or comparing snapshots that belong to different moments in time.

Per questo scenario di utilizzo, è anche possibile estendere le tabelle Product e Location perché diventino tabelle temporali, consentendo l'analisi successiva della cronologia delle modifiche di UnitPrice e NumberOfEmployee.For this usage scenario, you can also extend the Product and Location tables to become temporal tables, which enables later analysis of the history of changes of UnitPrice and NumberOfEmployee.

ALTER TABLE Product   
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   

ALTER TABLE Product    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));  

ALTER TABLE [Location]  
ADD   
    SysStartTime datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN    
        constraint DFValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
    , SysEndTime datetime2 (2)  GENERATED ALWAYS AS ROW END HIDDEN     
        constraint DFValidTo DEFAULT '9999.12.31 23:59:59.99'  
    , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);  

ALTER TABLE [Location]    
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));  

Poiché il modello di dati prevede ora più tabelle temporali, la procedura consigliata per l'analisi AS OF consiste nel creare una vista che estrae i dati necessari dalle tabelle correlate e applica FOR SYSTEM_TIME AS OF alla vista, semplificando significativamente la ricostruzione dello stato dell'intero modello di dati:Since the data model now involves multiple temporal tables, the best practice for AS OF analysis is to create a view that extracts necessary data from the related tables and apply FOR SYSTEM_TIME AS OF to the view as this will greatly simplify reconstructing the state of entire data model:

DROP VIEW IF EXISTS vw_ProductInventoryDetails;  
GO  

CREATE VIEW vw_ProductInventoryDetails  
AS  
    SELECT PrInv.ProductId ,PrInv.LocationId, P.ProductName, L.LocationName, PrInv.Quantity  
    , P.UnitPrice, L.NumberOfEmployees  
    , P.SysStartTime AS ProductStartTime, P.SysEndTime AS ProductEndTime  
    , L.SysStartTime AS LocationStartTime, L.SysEndTime AS LocationEndTime  
    , PrInv.SysStartTime AS InventoryStartTime, PrInv.SysEndTime AS InventoryEndTime  
FROM dbo.ProductInventory as PrInv  
JOIN dbo.Product AS P ON PrInv.ProductId = P.ProductID  
JOIN dbo.Location AS L ON PrInv.LocationId = L.LocationID;  
GO  
SELECT * FROM vw_ProductInventoryDetails  
    FOR SYSTEM_TIME AS OF '2015.01.01';   

L'immagine seguente illustra il piano di esecuzione generato per la query SELECT.The following picture shows the execution plan generated for the SELECT query. Mostra che tutta la complessità della gestione delle relazioni temporali viene completamente controllata dal motore di SQL Server:This illustrates that all complexity of dealing with temporal relations is fully handled by the SQL Server engine:

ASOFExecutionPlanASOFExecutionPlan

Usare il codice seguente per confrontare lo stato dell'inventario dei prodotti tra due punti nel tempo (un giorno fa e un mese fa):Use the following code to compare state of product inventory between two points in time (a day ago and a month ago):

DECLARE @dayAgo datetime2 (0) = DATEADD (day, -1, SYSUTCDATETIME());  
DECLARE @monthAgo datetime2 (0) = DATEADD (month, -1, SYSUTCDATETIME());  

SELECT   
    inventoryDayAgo.ProductId  
    , inventoryDayAgo.ProductName  
    , inventoryDayAgo.LocationName  
    , inventoryDayAgo.Quantity AS QuantityDayAgo,inventoryMonthAgo.Quantity AS QuantityMonthAgo  
    , inventoryDayAgo.UnitPrice AS UnitPriceDayAgo, inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo  
FROM vw_ProductInventoryDetails  
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo  
JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo  
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;  

Rilevamento di anomalieAnomaly Detection

Il rilevamento di anomalie, o rilevamento di outlier, è l'identificazione di elementi che non sono conformi a un modello previsto o altri elementi in un set di dati.Anomaly detection (or outlier detection) is the identification of items which do not conform to an expected pattern or other items in a dataset.
È possibile usare le tabelle temporali con controllo delle versioni di sistema per rilevare le anomalie che si verificano periodicamente o irregolarmente, perché è possibile usare query temporali per trovare rapidamente modelli specifici.You can use system-versioned temporal tables to detect anomalies that occur periodically or irregularly as you can utilize temporal querying to quickly locate specific patterns.
Il tipo di anomalie dipende dal tipo di dati raccolti e dalla logica di business.What anomaly is depends on type of data you collect and your business logic.

L'esempio seguente illustra la logica semplificata per rilevare "picchi" nelle cifre relative alle vendite.The following example shows simplified logic for detecting “spikes” in sales numbers. Si supponga di utilizzare una tabella temporale che raccoglie la cronologia dei prodotti acquistati:Let’s assume that you work with a temporal table that collects history of the products purchased:

CREATE TABLE [dbo].[Product]  
                (  
            [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED  
        , [ProductName] [varchar](100) NOT NULL  
        , [DailySales] INT NOT NULL  
        , [ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL  
        , [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL  
        , PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])  
    )  
    WITH( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ProductHistory]   
        , DATA_CONSISTENCY_CHECK = ON ))  

Il diagramma illustra gli acquisti nel tempo:The following diagram shows the purchases over time:

TemporalAnomalyDetectionTemporalAnomalyDetection

Presupponendo che nei giorni normali il numero di prodotti acquistati presenta una varianza ridotta, la query seguente identifica gli outlier singleton, ovvero campioni la cui differenza rispetto ai relativi vicini più prossimi è significativa (2x), mentre i campioni circostanti non presentano differenze significative (inferiori al 20%):Assuming that during the regular days number of purchased products has small variance, the following query identifies singleton outliers - samples which difference compared to their immediate neighbors is significant (2x), while surrounding samples do not differ significantly (less than 20%):

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)  
AS  
    (  
        SELECT   
            ProdId, LAG (DailySales, 1, 1) over (partition by ProdId order by ValidFrom) as PrevValue  
            , DailySales, LEAD (DailySales, 1, 1) over (partition by ProdId order by ValidFrom) as NextValue   
             , ValidFrom, ValidTo from Product  
        FOR SYSTEM_TIME ALL  
)  

SELECT   
    ProdId  
    , PrevValue  
    , CurrentValue  
    , NextValue  
    , ValidFrom  
    , ValidTo  
    , ABS (PrevValue - NextValue) / convert (float, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) as PrevToNextDiff  
    , ABS (CurrentValue - PrevValue) / convert (float, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) as CurrentToPrevDiff  
    , ABS (CurrentValue - NextValue) / convert (float, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) as CurrentToNextDiff  
FROM CTE   
    WHERE   
        ABS (PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2  
            AND ABS (CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2  
            AND ABS (CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;  
Nota

Questo esempio è intenzionalmente semplificato.This example is intentionally simplified. Negli scenari di produzione si useranno probabilmente metodi statistici avanzati per identificare i campioni che non seguono il modello comune.In the production scenarios, you would likely use advanced statistical methods to identify samples which do not follow the common pattern.

Dimensioni a modifica lentaSlowly-Changing Dimensions

Le dimensioni nel data warehousing in genere contengono dati relativamente statici sulle entità, ad esempio posizioni geografiche, clienti o prodotti.Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. Tuttavia, alcuni scenari richiedono di tenere traccia anche delle modifiche dei dati nelle tabelle delle dimensioni.However, some scenarios require you to track data changes in dimension tables as well. Considerato che la modifica nelle dimensioni avviene meno frequentemente, in modo imprevedibile e al di fuori della normale pianificazione degli aggiornamenti che si applica alle tabelle dei fatti, questi tipi di tabelle delle dimensioni sono dette dimensioni a modifica lenta.Given that modification in dimensions happen much less frequently, in unpredictable manner and outside of the regular update schedule that applies to fact tables, these types of dimension tables are called slowly changing dimensions (SCD).

Esistono diverse categorie di dimensioni a modifica lenta basate sulla modalità di mantenimento della cronologia delle modifiche:There are several categories of slowly changing dimensions based on how history of changes is preserved:

  • Tipo 0: la cronologia non viene mantenuta.Type 0: History is not preserved. Gli attributi delle dimensioni riflettono i valori originali.Dimension attributes reflect original values.

  • Tipo 1: gli attributi delle dimensioni riflettono i valori più recenti. I valori precedenti vengono sovrascritti.Type 1: Dimension attributes reflect latest values (previous values are overwritten)

  • Tipo 2: ogni versione del membro di dimensione è rappresentato con una riga separata nella tabella, in genere con colonne che rappresentano il periodo di validità.Type 2: Every version of dimension member represented with separate row in the table usually with columns that represent period of validity

  • Tipo 3: mantenimento di una cronologia limitata per gli attributi selezionati, usando colonne aggiuntive nella stessa riga.Type 3: Keeping limited history for selected attribute(s) using additional columns in the same row

  • Tipo 4: mantenimento della cronologia nella tabella separata, mentre la tabella delle dimensioni originale mantiene le versioni dei membri di dimensione più recenti (correnti).Type 4: Keeping history in the separate table while original dimension table keeps latest (current) dimension member versions

    Quando si sceglie una strategia basata su dimensioni a modifica lenta, è responsabilità del livello ETL (Extract-Transform-Load) mantenere tabelle delle dimensioni accurate, che in genere richiedono una notevole quantità di codice e una manutenzione complessa.When you choose SCD strategy, it is responsibility of the ETL layer (Extract-Transform-Load) to keep dimension table(s) accurate and that usually requires a lot of code and complex maintenance.

    Le tabelle temporali con controllo delle versioni di sistema in SQL Server 2016 possono essere usate per ridurre significativamente la complessità del codice, perché la cronologia dei dati viene mantenuta automaticamente.System-versioned temporal tables in SQL Server 2016 can be used to dramatically lower the complexity of your code as history of data is automatically preserved. Considerato che per la relativa implementazione si usano due tabelle, le tabelle temporali in SQL Server 2016 si avvicinano alle dimensioni a modifica lenta di tipo 4.Given its implementation using two tables, temporal tables in SQL Server 2016 is closest to Type 4 SCD. Tuttavia, poiché le query temporali consentono di fare riferimento solo alla tabella corrente, è anche possibile prendere in considerazione le tabelle temporali in ambienti in cui si prevede di usare dimensioni a modifica lenta di tipo 2.However, since temporal queries allows you to reference current table only, you can also consider temporal tables in environments where you plan to use Type 2 SCD.

    Per convertire una dimensione normale in dimensioni a modifica lenta, è sufficiente crearne una nuova o modificarne una esistente perché diventi una tabella temporale con controllo delle versioni di sistema.In order to convert your regular dimension to SCD, just create a new one or alter an existing one to become a system-versioned temporal table. Se la tabella delle dimensioni esistente contiene dati cronologici, creare una tabella separata e spostare i dati cronologici in tale tabella mantenendo le versioni delle dimensioni correnti (effettive) nella tabella delle dimensioni originale.If your existing dimension table contains historical data, create separate table and move historical data there and keep current (actual) dimension versions in your original dimension table. Usare quindi la sintassi ALTER TABLE per convertire la tabella delle dimensioni in una tabella temporale con controllo delle versioni di sistema con una tabella di cronologia predefinita.Then use ALTER TABLE syntax to convert your dimension table to a system-versioned temporal table with a predefined history table.

    L'esempio seguente illustra il processo presupponendo che la tabella delle dimensioni DimLocation abbia già ValidFrom e ValidTo come colonne datetime2, che non ammettono i valori Null, popolate dal processo ETL:The following example illustrates the process and assumes that the DimLocation dimension table already has ValidFrom and ValidTo as datetime2 non-nullable columns which are populated by the ETL process:

/*Move “closed” row versions into newly created history table*/  
SELECT * INTO  DimLocationHistory  
    FROM DimLocation  
        WHERE ValidTo < '9999-12-31 23:59:59.99';  
GO  
/*Create clustered columnstore index which is a very good choice in DW scenarios*/  
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory  
/*Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/  
DELETE FROM DimLocation  
    WHERE ValidTo < '9999-12-31 23:59:59.99';  
/*Add period definition*/  
ALTER TABLE DimLocation ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);  
/*Enable system-versioning and bind histiory table to the DimLocation*/  
ALTER TABLE DimLocation SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));  

Si noti che non è necessario codice aggiuntivo per mantenere le dimensioni a modifica lenta durante il processo di caricamento del data warehouse dopo che è stato creato.Note that Nno additional code is required to maintain SCD during the data warehouse loading process once you created it.

L'illustrazione seguente mostra come usare le tabelle temporali in uno scenario semplice che include 2 dimensioni a modifica lenta (DimLocation e DimProduct) e una tabella dei fatti.The following illustration shows how you can use Temporal Tables in a simple scenario involving 2 SCDs (DimLocation and DimProduct) and one fact table.

TemporalSCDTemporalSCD

Per usare le precedenti dimensioni a modifica lenta nei report, è necessario modificare in modo efficace l'esecuzione delle query.In order to use above SCDs in reports, you need to effectively adjust querying. Ad esempio, è possibile calcolare l'importo totale delle vendite e il numero medio dei prodotti venduti pro capite per gli ultimi sei mesi.For example, you might want to calculate the total sales amount and the average number of sold products per capita for the last six months. Si noti che entrambe le metriche richiedono la correlazione dei dati tra la tabella dei fatti e le dimensioni i cui attributi importanti per l'analisi (DimLocation.NumOfCustomers, DimProduct.UnitPrice) potrebbero essere stati modificati.Note that both metrics requires the correlation of data from the fact table and dimensions that might have changed their attributes important for the analysis (DimLocation.NumOfCustomers, DimProduct.UnitPrice). La query seguente calcola correttamente le metriche necessarie:The followinq query properly calculates the required metrics:

DECLARE @now datetime2 = SYSUTCDATETIME()  
DECLARE @sixMonthsAgo datetime2 SET   
    @sixMonthsAgo = DATEADD (month, -12, SYSUTCDATETIME())   

SELECT DimProduct_History.ProductId  
   , DimLocation_History.LocationId  
    , SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount  
    , AVG (F.Quantity/DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita   
FROM FactProductSales F   
/* find corresponding record in SCD history in last 6 months, based on matching fact */  
JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History   
    ON DimLocation_History.LocationId = F.LocationId   
        AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo   
/* find corresponding record in SCD history in last 6 months, based on matching fact */  
JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History   
    ON DimProduct_History.ProductId = F.ProductId  
        AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo   
    WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now   
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId ;  

Considerazioni:Considerations:

  • L'uso di tabelle temporali con controllo delle versioni di sistema per le dimensioni a modifica lenta è accettabile se il periodo di validità calcolato in base all'ora della transazione di database è appropriato per la logica di business.Using system-versioned temporal tables for SCD is acceptable if period of validity calculated based on database transaction time is fine with your business logic. Se si caricano dati con un ritardo significativo, l'ora della transazione potrebbe non essere accettabile.If you load data with significant delay, transaction time might not be acceptable.

  • Per impostazione predefinita, le tabelle temporali con controllo delle versioni di sistema non consentono la modifica dei dati cronologici dopo il caricamento. È possibile modificare la cronologia dopo l'impostazione dell'attributo SYSTEM_VERSIONING su OFF.By default, system-versioned temporal tables do not allow changing historical data after loading (you can modify history after you set SYSTEM_VERSIONING to OFF). Potrebbe essere una limitazione nei casi in cui la modifica dei dati cronologici viene eseguita regolarmente.This might be limitation in cases where changing historical data happens regularly.

  • Le tabelle temporali con controllo delle versioni di sistema generano una versione di riga a seguito di qualsiasi modifica della colonna.Temporal system-versioned tables generate row version on any column change. Se si vuole evitare la generazione di nuove versioni per determinate modifiche della colonna, è necessario incorporare tale limitazione nella logica di ETL.If you want to suppress new versions on certain column change you need to incorporate that limitation in the ETL logic.

  • Se si prevede un numero significativo di righe della cronologia nelle tabelle di dimensioni a modifica lenta, considerare l'uso di un indice columnstore cluster come opzione di archiviazione principale per la tabella di cronologia.If you expect a significant number of historical rows in SCD tables, consider using a clustered columnstore index as the main storage option for history table. In questo modo si ridurrà l'impatto l'impatto sulla tabella di cronologia, velocizzando le query analitiche.That will reduce history table footprint and speed up your analytical queries.

Ripristino dal danneggiamento dei dati a livello di rigaRepairing Row-Level Data Corruption

È possibile basarsi su dati cronologici nelle tabelle temporali con controllo delle versioni di sistema per ripristinare rapidamente singole righe a uno degli stati acquisiti in precedenza.You can rely on historical data in system-versioned temporal tables to quickly repair individual rows to any of the previously captured states. Questa proprietà delle tabelle temporali è molto utile quando è possibile trovare le righe interessate e/o quando si conosce l'ora della modifica indesiderata dei dati, per eseguire un ripristino in modo molto efficiente senza usare backup.This property of temporal tables is very useful when you are able to locate affected rows and/or when you know time of undesired data change so you can perform repair very efficiently without dealing with backups.

Questo approccio presenta diversi vantaggi:This approach has several advantages:

  • È possibile controllare con grande precisione l'ambito del ripristino.You are able to control the scope of the repair very precisely. I record non interessati devono essere mantenuti nello stato più recente, che è spesso un requisito fondamentale.Records that are not affected need to stay at the latest state, which is often a critical requirement.

  • L'operazione è molto efficiente e il database rimane online per tutti i carichi di lavoro che usano i dati.Operation is very efficient and the database stays online for all workloads using the data.

  • Alla stessa operazione di ripristino viene applicato il controllo delle versioni.The repair operation itself is versioned. È disponibile l'audit trail per l'operazione di ripristino, quindi è possibile analizzare cosa avviene successivamente, se necessario.You have audit trail for repair operation itself, so you can analyze what happened later if necessary.

    L'azione di ripristino può essere automatizzata in modo relativamente semplice.Repair action can be automated relatively easily. Ecco l'esempio di codice della stored procedure che esegue il ripristino dei dati per la tabella Employee usata nello scenario di controllo dei dati.Here is code example of the stored procedure that performs data repair for the table Employee used in the data audit scenario.

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;  
GO  

CREATE PROCEDURE sp_RepairEmployeeRecord   
    @EmployeeID INT,  
    @versionNumber INT = 1  
AS  

;WITH History  
AS  
(  
        /* Order historical rows by tehir age in DESC order*/  
        SELECT ROW_NUMBER () OVER (PARTITION BY EmployeeID ORDER BY [ValidTo] DESC) AS RN, *  
        FROM Employee FOR SYSTEM_TIME ALL WHERE YEAR (ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID  
)  

/*Update current row by using N-th row version from history (default is 1 - i.e. last version)*/  
UPDATE Employee   
    SET [Position] = H.[Position], [Department] = H.Department, [Address] = H.[Address], AnnualSalary = H.AnnualSalary  
    FROM Employee E JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber  
    WHERE E.EmployeeID = @EmployeeID  

Questa stored procedure accetta @EmployeeID e @versionNumber come parametri di input.This stored procedure takes @EmployeeID and @versionNumber as input parameters. Per impostazione predefinita, questa stored procedure consente di ripristinare lo stato della riga all'ultima versione dalla cronologia (@versionNumber = 1).This procedure by default restores row state to the last version from the history (@versionNumber = 1).

L'immagine seguente mostra lo stato della riga prima e dopo la chiamata della stored procedure.The following picture shows state of the row before and after the procedure invocation. Il rettangolo rosso contrassegna la versione della riga corrente non corretta, mentre il rettangolo verde contrassegna la versione corretta dalla cronologia.Red rectangle marks current row version that is incorrect, while green rectangle marks correct version from the history.

TemporalUsageRepair1TemporalUsageRepair1

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1  

TemporalUsageRepair2TemporalUsageRepair2

Questa stored procedure di ripristino può essere definita in modo che accetti un timestamp esatto invece della versione di riga.This repair stored procedure can be defined to accept an exact timestamp instead of row version. Ripristinerà la riga a una qualsiasi versione attiva per il punto nel tempo specificato, ovvero AS OF punto nel tempo.It will restore row to any version that was active for the point in time provided (i.e. AS OF point in time).

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;  
GO  

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf   
    @EmployeeID INT,  
    @asOf datetime2  
AS  

/*Update current row to the state that was actual AS OF provided date*/  
UPDATE Employee   
    SET [Position] = History.[Position], [Department] = History.Department, [Address] = History.[Address], AnnualSalary = History.AnnualSalary  
    FROM Employee AS E JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History  ON E.EmployeeID = History.EmployeeID  
    WHERE E.EmployeeID = @EmployeeID  

Per lo stesso campione di dati l'immagine seguente illustra uno scenario di ripristino con una condizione temporale.For the same data sample the following picture illustrates repair scenario with time condition. Sono evidenziati il parametro @asOf, la riga selezionata nella cronologia effettiva al punto nel tempo specificato e la nuova versione di riga nella tabella corrente dopo l'operazione di ripristino:Highlighted are @asOf parameter, selected row in the history that was actual at the provided point in time and new row version in the current table after repair operation:

TemporalUsageRepair3TemporalUsageRepair3

La correzione dei dati può diventare una parte automatizzata del caricamento dei dati nei sistemi di data warehousing e segnalazione.Data correction can become part of automated data loading in data warehousing and reporting systems.
Se un valore appena aggiornato non è corretto, in molti scenari il ripristino della versione precedente dalla cronologia è una soluzione adeguata.If a newly updated value is not correct then, in many scenarios, restoring the previous version from history is good enough mitigation. Il diagramma seguente illustra come è possibile automatizzare il processo:The following diagram shows how this process can be automated:

TemporalUsageRepair4TemporalUsageRepair4

Vedere ancheSee Also

Tabelle temporali Temporal Tables
Introduzione alle tabelle temporali con controllo delle versioni di sistema Getting Started with System-Versioned Temporal Tables
Verifiche di coerenza del sistema della tabella temporale Temporal Table System Consistency Checks
Partizionamento con le tabelle temporali Partitioning with Temporal Tables
Considerazioni e limitazioni delle tabelle temporali Temporal Table Considerations and Limitations
Sicurezza di una tabella temporale Temporal Table Security
Tabelle temporali con controllo delle versioni di sistema con tabelle con ottimizzazione per la memoria System-Versioned Temporal Tables with Memory-Optimized Tables
Funzioni e viste per i metadati delle tabelle temporaliTemporal Table Metadata Views and Functions