Scénarios d’utilisation de table temporelleTemporal Table Usage Scenarios

CETTE RUBRIQUE S’APPLIQUE À :ouiSQL Server (à partir de la version 2008)nonAzure SQL DatabasenonAzure SQL Data Warehouse nonParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Les tables temporelles sont généralement utiles dans les scénarios qui exigent un suivi de l’historique des modifications de données.Temporal Tables are generally useful in scenarios that require tracking history of data changes.
Nous vous recommandons d’envisager les tables temporelles dans les cas d’usage suivants, car elles offrent des avantages importants au niveau de la productivité.We recommend you to consider Temporal Tables in the following use cases for major productivity benefits.

Audit des donnéesData Audit

Utilisez la gestion système des versions temporelle sur les tables qui stockent des informations critiques pour lesquelles vous devez effectuer le suivi de ce qui a changé et des moments où les modifications ont eu lieu, et mener des investigations légales sur les données à tout moment.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.
Les tables avec version système temporelles vous permettent de planifier les scénarios d’audit de données dans les premières étapes du cycle de développement ou d’ajouter un audit de données à des applications ou solutions existantes quand vous en avez besoin.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.

Le diagramme suivant illustre un scénario avec une table Employee, dont l’échantillon de données comprend des versions de ligne actuelles (bleu) et historiques (gris).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 partie droite du diagramme indique les versions de ligne sur l’axe du temps et les lignes qui sont sélectionnées avec différents types de requêtes sur la table temporelle avec ou sans la clause 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

Activation de la gestion système des versions sur une nouvelle table en vue d’un audit des donnéesEnabling system-versioning on a new table for data audit

Si vous avez identifié des informations qui exigent un audit de données, créez des tables de base de données en tant que tables avec version système temporelles.If you have identified information that needs data audit, create database tables as temporal system-versioned. L’exemple simple suivant illustre un scénario dont les informations se trouvent dans une table Employee appartenant à une base de données de ressources humaines hypothétique :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));  

Diverses options pour créer une table avec gestion système des versions temporelle sont décrites dans Création d’une table temporelle avec versions gérées par le système.Various options to create temporal system-versioned table are described in Creating a System-Versioned Temporal Table.

Activation de la gestion système des versions sur une table existante en vue d’un audit des donnéesEnabling system-versioning on an existing table for data audit

Si vous avez besoin d’effectuer un audit des données dans des bases de données existantes, utilisez ALTER TABLE pour convertir les tables non temporelles en tables avec version système.If you need to perform data audit in existing databases, use ALTER TABLE to extend non-temporal tables to become system-versioned. Pour préserver votre application, ajoutez des colonnes de période avec l’option HIDDEN, comme expliqué dans Modifier une table non temporelle pour la convertir en table temporelle avec versions gérées par le système.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’exemple suivant illustre l’activation de la gestion système des versions sur une table Employee existante appartenant à une base de données de ressources humaines hypothétique :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));  

Une fois exécuté le script ci-dessus, toutes les modifications de données sont collectées en toute transparence dans la table de l’historique.After executing the above script, all data changes will be collected transparently in the history table.
Dans un scénario d’audit de données standard, vous souhaitez connaître toutes les modifications de données qui ont été appliquées à une ligne spécifique au cours d’une période digne d’intérêt.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 table de l’historique par défaut est créée avec un arbre B (B-tree) rowstore cluster pour traiter efficacement ce cas d’usage.The default history table is created with clustered row-store B-Tree to efficiently address this use case.

Analyse des donnéesPerforming data analysis

Une fois que vous avez activé la gestion système des versions à l’aide d’une des méthodes ci-dessus, une requête vous suffit pour lancer l’audit des données.After enabling system-versioning using either of the above approaches, data audit is just one query away from you. La requête suivante recherche les versions de ligne dans la table Employee pour lesquelles EmployeeID vaut 1000 et qui ont été actives pendant au moins une partie de la période comprise entre le 1er janvier 2014 et le 1er janvier 2015 (limite supérieure comprise) :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;  

Remplacez FOR SYSTEM_TIME BETWEEN...AND par FOR SYSTEM_TIME ALL pour analyser l’historique complet des modifications de données pour cet employé :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;  

Pour rechercher les versions de ligne qui étaient actives uniquement pendant une période (et pas en dehors de celle-ci), utilisez CONTAINED IN.To search for row versions that were active only within a period (and not outside of it), use CONTAINED IN. Cette requête est très efficace, car elle interroge uniquement la table de l’historique :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;  

Enfin, dans certains scénarios d’audit, vous pouvez voir l’aspect que présentait une table entière à n’importe quel point passé dans le temps :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' ;  

Les tables temporelles avec version système stockent des valeurs pour les colonnes de période dans le fuseau horaire UTC, même s’il est toujours plus pratique d’utiliser le fuseau horaire local à la fois pour le filtrage des données et l’affichage des résultats.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’exemple de code suivant montre comment appliquer la condition de filtrage qui est spécifiée à l’origine dans le fuseau horaire local puis convertie au format UTC à l’aide de l’instruction AT TIME ZONE introduite dans 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’instruction AT TIME ZONE est utile dans tous les autres scénarios faisant appel à des tables avec version système.Using AT TIME ZONE is helpful in all other scenarios where system-versioned tables are used.

Conseil

Les conditions de filtrage spécifiées dans des clauses temporelles avec FOR SYSTEM_TIME sont dites « SARG-able » ; en d’autres termes,Filtering conditions specified in temporal clauses with FOR SYSTEM_TIME are SARG-able (i.e SQL Server peut utiliser un index cluster sous-jacent pour effectuer une recherche au lieu d’une opération d’analyse.SQL Server can utilize underlying clustered index to perform a seek instead of a scan operation.
Si vous interrogez directement la table de l’historique, vérifiez que votre condition de filtrage est également « SARG-able » en spécifiant des filtres sous la forme <colonne de période> {< | > | =, …}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> {< | > | =, …} condition_date AT TIME ZONE ‘UTC’.date_condition AT TIME ZONE ‘UTC’.
Si vous appliquez AT TIME ZONE à des colonnes de période, SQL Server effectue une analyse de table/index, qui peut être très coûteuse.If you apply AT TIME ZONE to period columns, SQL Server will perform a table/index scan, which can be very expensive. Évitez ce type de condition dans vos requêtes :Avoid this type of condition in your queries:
<colonne de période> AT TIME ZONE '<votre fuseau horaire>' > {< | > | =, …}<period column> AT TIME ZONE ‘<your time zone>’ > {< | > | =, …} condition_date.date_condition.

Voir aussi Interrogation des données dans une table temporelle avec versions gérées par le système.See also: Querying Data in a System-Versioned Temporal Table.

Analyses à un point dans le temps (voyage dans le temps)Point in Time Analysis (Time Travel)

À la différence de l’audit de données, qui se concentre essentiellement sur les modifications apportées à des enregistrements individuels, les scénarios de voyage dans le temps permettent aux utilisateurs de voir comment des jeux de données entiers changent au fil du temps.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. Parfois, le voyage dans le temps fait appel à plusieurs tables temporelles connexes, chacune évoluant à son propre rythme, dont vous pouvez analyser les éléments suivants :Sometimes time travel includes several related temporal tables, each changing at independent pace, for which you want to analyze:

  • Tendances des indicateurs importants dans les données historiques et les données actuellesTrends for the important indicators in the historical and current data

  • Instantané exact de la totalité des données « depuis » (AS OF) n’importe quel point passé dans le temps (hier, il y a un mois , etc.)Exact snapshot of the entire data “as of” any point in time in the past (yesterday, a month ago, etc.)

  • Différences entre deux points dans le temps dignes d’intérêt (il y a un mois et il y a trois mois, par exemple)Differences in between two point in time of interest (a month ago vs. three months ago, for instance)

    Il existe plusieurs scénarios concrets qui exigent une analyse de voyage dans le temps.There are many real-world scenarios which require time travel analysis. Pour illustrer ce scénario d’utilisation, nous allons examiner OLTP avec l’historique généré automatiquement.To illustrate this usage scenario, let's look at OLTP with auto-generated history.

OLTP avec l’historique des données généré automatiquementOLTP with Auto-Generated Data History

Dans les systèmes de traitement transactionnel, il n’est pas rare d’analyser l’évolution de métriques importantes dans le temps.In transaction processing systems, it is not unusual to analyze how important metrics change over time. Dans l’idéal, l’analyse de l’historique ne doit pas compromettre les performances de l’application OLTP, où l’accès à l’état des données le plus récent doit se produire avec une latence et un verrouillage des données minimaux.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. Les tables avec version système temporelles sont conçues pour permettre aux utilisateurs de conserver en toute transparence l’historique complet des modifications en vue d’une analyse ultérieure, séparément des données actuelles, avec un impact minime sur la charge de travail 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.
Pour les charges de travail de traitement transactionnel élevées, nous vous recommandons d’utiliser des tables temporelles avec version gérée par le système avec tables à mémoire optimisée; ainsi, vous pouvez stocker les données actuelles en mémoire et l’historique complet des modifications sur le disque à moindres coûts.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.

Pour la table de l’historique, nous vous recommandons d’utiliser un index columnstore cluster pour les raisons suivantes :For the history table, we recommend that you use a clustered columnstore index for the following reasons:

  • L’analyse de tendances classique bénéficie des performances des requêtes que procure un index columnstore cluster.Typical trend analysis benefits from query performance provided by a clustered columnstore index.

  • La tâche de vidage des données avec des tables optimisées en mémoire fonctionne mieux sous une lourde charge de travail OLTP quand la table de l’historique a un index 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 index columnstore cluster fournit une excellente compression, surtout dans les scénarios où toutes les colonnes ne sont pas modifiées en même temps.A clustered columnstore index provides excellent compression, especially in scenarios where not all columns are changed at the same time.

    Utiliser des tables temporelles avec l’OLTP en mémoire réduit le besoin de conserver la totalité du jeu de données en mémoire et vous permet de différencier facilement les données à chaud des données à froid.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.
    Parmi les exemples de scénarios concrets qui rentrent dans cette catégorie, citons la gestion des stocks ou la négociation en devises.Examples of the real-world scenarios that fit well into this category are inventory management or currency trading, among others.

    Le diagramme suivant montre un modèle de données simplifié utilisé pour la gestion de stocks :The following diagram shows simplified data model used for inventory management:

    TemporalUsageInMemoryTemporalUsageInMemory

    L’exemple de code suivant crée la table ProductInventory comme table temporelle avec version système en mémoire avec un index columnstore cluster sur la table de l’historique (qui remplace en fait l’index rowstore créé par défaut) :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):

Note

Vérifiez que votre base de données permet de créer des tables optimisées en mémoire.Make sure that your database allows creation of memory-optimized tables. Consultez Création d’une table mémoire optimisée et d’une procédure stockée compilée en mode natif.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);  

Pour le modèle ci-dessus, la procédure de gestion des stocks pourrait ressembler à ceci :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 procédure stockée spUpdateInventory insère un nouveau produit dans l’inventaire ou met à jour la quantité du produit pour l’emplacement spécifique.The spUpdateInventory stored procedure either inserts a new product in the inventory or updates the product quantity for the particular location. La logique métier est très simple et consiste à maintenir le dernier état précis tout le temps en incrémentant/décrémentant le champ Quantity par le biais de la mise à jour de la table, tandis que les tables avec version système ajoutent en toute transparence une dimension d’historique aux données, comme l’illustre le diagramme ci-dessous.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

À présent, l’interrogation du dernier état peut être effectuée efficacement dans le module compilé en mode natif :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’analyse des modifications des données au fil du temps devient très facile avec la clause FOR SYSTEM_TIME ALL, comme l’illustre l’exemple suivant :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;  

Le diagramme suivant montre, pour un produit, l’historique des données, que vous pouvez afficher facilement en important la vue ci-dessus dans Power Query, Power BI ou un outil décisionnel similaire :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

Les tables temporelles peuvent être utilisées dans ce scénario pour effectuer d’autres types d’analyse de voyage dans le temps, tels que la reconstruction de l’état de l’inventaire à partir de n’importe quel point passé dans le temps (AS OF) ou la comparaison d’instantanés qui appartiennent à différents moments dans le temps.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.

Pour ce scénario d’utilisation, vous pouvez également convertir les tables Product et Location en tables temporelles, en vue d’analyser l’historique des modifications des données UnitPrice et 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));  

Étant donné que le modèle de données implique maintenant plusieurs tables temporelles, la meilleure pratique pour une analyse AS OF consiste à créer une vue qui extrait les données nécessaires des tables connexes et à appliquer FOR SYSTEM_TIME AS OF à la vue, ce qui permet de simplifier sensiblement la reconstruction de l’état du modèle de données entier :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’illustration suivante montre le plan d’exécution généré pour la requête SELECT.The following picture shows the execution plan generated for the SELECT query. Comme vous pouvez le constater, le moteur SQL Server prend en charge toute la complexité de la gestion des relations temporelles :This illustrates that all complexity of dealing with temporal relations is fully handled by the SQL Server engine:

ASOFExecutionPlanASOFExecutionPlan

Le code suivant permet de comparer l’état du stock de produits entre deux points dans le temps (il y a un jour et il y a un mois) :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;  

Détection d’anomalieAnomaly Detection

La détection d’anomalies (ou détection de valeurs hors norme) est l’identification d’éléments qui ne sont pas conformes à un modèle attendu ou à d’autres éléments dans un jeu de données.Anomaly detection (or outlier detection) is the identification of items which do not conform to an expected pattern or other items in a dataset.
Vous pouvez utiliser des tables temporelles avec version système pour détecter les anomalies qui se produisent régulièrement ou irrégulièrement, et vous pouvez effectuer des interrogations temporelles pour localiser rapidement des modèles spécifiques.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.
Le type de données que vous collectez et votre logique métier déterminent la nature des anomalies.What anomaly is depends on type of data you collect and your business logic.

L’exemple suivant montre une logique simplifiée pour la détection des « pics » dans les chiffres de ventes.The following example shows simplified logic for detecting “spikes” in sales numbers. Supposons que vous travaillez avec une table temporelle qui collecte l’historique des produits achetés :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 ))  

Le diagramme suivant montre les achats dans le temps :The following diagram shows the purchases over time:

TemporalAnomalyDetectionTemporalAnomalyDetection

En supposant que le nombre de produits achetés varie peut pendant les jours normaux, la requête suivante identifie les valeurs hors norme de singleton ; les échantillons présentent une différence significative (x2) par rapport à leurs voisins immédiats, tandis que les échantillons environnants ne diffèrent pas considérablement (moins de 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;  

Note

Cet exemple est intentionnellement simplifié.This example is intentionally simplified. Dans les scénarios de production, vous utilisez généralement des méthodes statistiques avancées pour identifier les échantillons qui ne suivent pas le modèle commun.In the production scenarios, you would likely use advanced statistical methods to identify samples which do not follow the common pattern.

Dimensions à variation lenteSlowly-Changing Dimensions

En règle générale, les dimensions d’entreposage de données contiennent des données relativement statiques sur les entités telles que des produits, des clients ou des emplacements géographiques.Dimensions in data warehousing typically contain relatively static data about entities such as geographical locations, customers, or products. Toutefois, dans certains scénarios, vous devez également tracer les modifications de données dans des tables de dimension.However, some scenarios require you to track data changes in dimension tables as well. Étant donné que toute modification de dimensions se produit beaucoup moins fréquemment, de manière imprévisible et en dehors de la planification des mises à jour normales qui s’applique aux tables de faits, ces types de tables de dimension sont appelés dimensions à variation lente.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).

Il existe plusieurs catégories de dimensions à variation lente, selon la façon dont l’historique des modifications est conservé :There are several categories of slowly changing dimensions based on how history of changes is preserved:

  • Type 0 : l’historique n’est pas conservé.Type 0: History is not preserved. Les attributs de dimension reflètent les valeurs d’origine.Dimension attributes reflect original values.

  • Type 1 : les attributs de dimension reflètent les valeurs les plus récentes (les valeurs précédentes sont remplacées)Type 1: Dimension attributes reflect latest values (previous values are overwritten)

  • Type 2 : chaque version de membre de dimension représentée par une ligne distincte dans la table, généralement avec des colonnes qui représentent la période de validitéType 2: Every version of dimension member represented with separate row in the table usually with columns that represent period of validity

  • Type 3 : conservation d’un historique limité pour des attributs sélectionnés en utilisant des colonnes supplémentaires dans la même ligneType 3: Keeping limited history for selected attribute(s) using additional columns in the same row

  • Type 4 : conservation de l’historique dans la table distincte tandis que la table de dimension d’origine conserve les dernières versions des membres de dimension (actuelles)Type 4: Keeping history in the separate table while original dimension table keeps latest (current) dimension member versions

    Quand vous choisissez la stratégie de dimension à variation lente, il revient à la couche ETL (extraction, transformation et chargement) d’assurer l’exactitude des tables de dimension, ce qui exige généralement beaucoup de code et une maintenance complexe.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.

    Grâce aux tables temporelles avec version système dans SQL Server 2016, vous pouvez réduire considérablement la complexité de votre code dans la mesure où l’historique des données est conservé automatiquement.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. La mise en œuvre reposant sur deux tables, les tables temporelles dans SQL Server 2016 sont plus proches de la dimension à variation lente de type 4.Given its implementation using two tables, temporal tables in SQL Server 2016 is closest to Type 4 SCD. Toutefois, étant donné que les requêtes temporelles vous permettent de référencer la table actuelle uniquement, vous pouvez également envisager des tables temporelles dans les environnements où vous prévoyez d’utiliser la dimension à variation lente de type 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.

    Pour convertir votre dimension normale en dimension à variation lente, il vous suffit de créer une dimension ou d’en convertir une en table temporelle avec version système.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. Si votre table de dimension existante contient des données historiques, créez une table distincte, déplacez-y les données historiques, puis conservez les versions de dimension actuelles (réelles) dans votre table de dimension d’origine.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. Ensuite, utilisez la syntaxe ALTER TABLE pour convertir votre table de dimension en table temporelle avec version système avec une table de l’historique prédéfinie.Then use ALTER TABLE syntax to convert your dimension table to a system-versioned temporal table with a predefined history table.

    L’exemple suivant illustre ce processus et suppose que la table de dimension DimLocation possède déjà ValidFrom et ValidTo en tant que colonnes datetime2 remplies par le processus ETL et n’acceptant pas de valeurs NULL :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));  

Notez qu’aucun code supplémentaire n’est nécessaire pour gérer la dimension à variation lente pendant le processus de chargement de l’entrepôt de données, une fois la création effectuée.Note that no additional code is required to maintain SCD during the data warehouse loading process once you created it.

L’illustration suivante montre comment vous pouvez utiliser des tables temporelles dans un scénario simple impliquant 2 dimensions à variation lente (DimLocation et DimProduct) et une table de faits.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

Pour utiliser les dimensions à variation lente ci-dessus dans les rapports, vous devez paramétrer l’interrogation de manière efficace.In order to use above SCDs in reports, you need to effectively adjust querying. Par exemple, vous souhaiterez calculer le montant total des ventes et le nombre moyen de produits vendus par habitant au cours des six derniers mois.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. Notez que les deux métriques impliquent la corrélation de données à partir de la table de faits et des dimensions dont les attributs importants pour l’analyse ont pu évoluer (DimLocation.NumOfCustomers, DimProduct.UnitPrice).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 requête suivante calcule correctement les métriques requises :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 ;  

Éléments à prendre en considération :Considerations:

  • L’utilisation de tables temporelles avec version système pour la dimension à variation lente est acceptable si la période de validité calculée selon l’heure de transaction de base de données est appropriée pour votre logique métier.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. Si vous chargez des données avec un délai important, l’heure de transaction peut ne pas être acceptable.If you load data with significant delay, transaction time might not be acceptable.

  • Par défaut, les tables temporelles avec version système n’autorisent pas la modification des données historiques après le chargement (vous pouvez modifier l’historique après avoir défini SYSTEM_VERSIONING sur 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). Cela peut constituer une limitation dans les cas où les données historiques sont régulièrement modifiées.This might be limitation in cases where changing historical data happens regularly.

  • Les tables temporelles avec version système génèrent une version de ligne à chaque modification de la colonne.Temporal system-versioned tables generate row version on any column change. Si vous souhaitez supprimer les nouvelles versions à l’occasion d’une modification de colonne spécifique, vous devez intégrer cette limitation à la logique ETL.If you want to suppress new versions on certain column change you need to incorporate that limitation in the ETL logic.

  • Si vous prévoyez un nombre important de lignes d’historique dans les tables de dimension à variation lente, envisagez d’utiliser un index columnstore cluster comme option de stockage principal pour la table de l’historique.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. Ainsi, vous réduisez l’encombrement de la table de l’historique et accélérez vos requêtes analytiques.That will reduce history table footprint and speed up your analytical queries.

Réparation d’une altération des données au niveau des lignesRepairing Row-Level Data Corruption

Vous pouvez vous baser sur les données historiques des tables temporelles avec version système pour rétablir rapidement des lignes individuelles dans tout état précédemment capturé.You can rely on historical data in system-versioned temporal tables to quickly repair individual rows to any of the previously captured states. Cette propriété des tables temporelles est très utile quand vous pouvez localiser les lignes affectées et/ou que vous connaissez l’heure de la modification de données non souhaitée ; vous pouvez ainsi effectuer la réparation très efficacement sans faire appel à des sauvegardes.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.

Cette approche présente plusieurs avantages :This approach has several advantages:

  • Vous pouvez contrôler très précisément l’étendue de la réparation.You are able to control the scope of the repair very precisely. Les enregistrements qui ne sont pas affectés doivent demeurer au dernier état, condition souvent essentielle.Records that are not affected need to stay at the latest state, which is often a critical requirement.

  • L’opération est très efficace et la base de données reste en ligne pour toutes les charges de travail utilisant des données.Operation is very efficient and the database stays online for all workloads using the data.

  • L’opération de réparation elle-même fait l’objet d’une gestion des versions.The repair operation itself is versioned. Comme vous disposez d’une piste d’audit pour l’opération de réparation elle-même, vous pouvez analyser ce qu’il s’est passé ultérieurement si nécessaire.You have audit trail for repair operation itself, so you can analyze what happened later if necessary.

    L’action de réparation peut être automatisée relativement facilement.Repair action can be automated relatively easily. Voici un exemple de code de la procédure stockée qui effectue la réparation des données de la table Employee utilisée dans le scénario d’audit de données.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  

Cette procédure stockée accepte @EmployeeID et @versionNumber en tant que paramètres d’entrée.This stored procedure takes @EmployeeID and @versionNumber as input parameters. Cette procédure restaure par défaut l’état de la ligne à la dernière version de l’historique (@versionNumber = 1).This procedure by default restores row state to the last version from the history (@versionNumber = 1).

L’illustration suivante montre l’état de la ligne avant et après l’appel de la procédure.The following picture shows state of the row before and after the procedure invocation. Le rectangle rouge indique la version de ligne actuelle qui est incorrecte, tandis que le rectangle vert indique la version correcte de l’historique.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

Cette procédure stockée de réparation peut être définie pour accepter un horodatage exact au lieu de la version de ligne.This repair stored procedure can be defined to accept an exact timestamp instead of row version. Dans ce cas, elle restaure la ligne à n’importe quelle version qui était active pour le point dans le temps fourni (autrement dit, AS OF point dans le temps).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  

Pour le même exemple de données, l’image suivante illustre le scénario de réparation avec une condition de temps.For the same data sample the following picture illustrates repair scenario with time condition. Sont mis en valeur le paramètre @asOf, une ligne sélectionnée dans l’historique qui était réelle au point dans le temps fourni et la nouvelle version de ligne dans la table actuelle après l’opération de réparation :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 correction des données peut être intégrée au processus de chargement automatisé des données dans les systèmes d’entreposage de données et de rapports.Data correction can become part of automated data loading in data warehousing and reporting systems.
Si une valeur qui vient d’être mise à jour n’est pas correcte, dans de nombreux scénarios, restaurer la version précédente à partir de l’historique peut suffire.If a newly updated value is not correct then, in many scenarios, restoring the previous version from history is good enough mitigation. Le diagramme suivant montre comment ce processus peut être automatisé :The following diagram shows how this process can be automated:

TemporalUsageRepair4TemporalUsageRepair4

Voir aussiSee Also

Tables temporelles Temporal Tables
Prise en main des tables temporelles avec versions gérées par le système Getting Started with System-Versioned Temporal Tables
Vérifications de cohérence système des tables temporelles Temporal Table System Consistency Checks
Partitionnement des tables temporelles Partitioning with Temporal Tables
Considérations et limitations liées aux tables temporelles Temporal Table Considerations and Limitations
Sécurité de la table temporelle Temporal Table Security
Tables temporelles avec version gérée par le système avec tables à mémoire optimisée System-Versioned Temporal Tables with Memory-Optimized Tables
Vues et fonctions de métadonnées de table temporelleTemporal Table Metadata Views and Functions