Tables temporellesTemporal tables

S’APPLIQUE À : ouiSQL Server 2016 et versions ultérieures ouiAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server 2016 and later yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL Server 2016 introduit la prise en charge des tables temporelles (également appelées tables temporelles avec version gérée par le système) comme fonctionnalité de base de données, qui offre une prise en charge intégrée de la fourniture d’informations sur les données stockées dans la table à tout moment, et non pas seulement les données correctes au moment présent.SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. La fonctionnalité temporelle est une fonctionnalité de base de données introduite dans la norme ANSI SQL 2011.Temporal is a database feature that was introduced in ANSI SQL 2011.

Démarrage rapideQuickstart

Qu’est-ce qu’une table temporelle versionnée par le systèmeWhat is a system-versioned temporal table

Une table temporelle avec version contrôlée par le système est un type de table utilisateur conçu pour conserver un historique complet des modifications apportées aux données et permettre l’analyse à un point dans le temps.A system-versioned temporal table is a type of user table designed to keep a full history of data changes and allow easy point in time analysis. Ce type de table temporelle est appelée table temporelle avec version gérée par le système, car la période de validité de chaque ligne est gérée par le système (c’est-à-dire le moteur de base de données).This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

Chaque table temporelle contient deux colonnes définies explicitement, chacune d’elles contenant un type de données datetime2 .Every temporal table has two explicitly defined columns, each with a datetime2 data type. Ces colonnes sont appelées colonnes de période.These columns are referred to as period columns. Ces colonnes de période sont utilisées de manière exclusive par le système pour enregistrer la période de validité de chaque ligne lorsqu’une ligne est modifiée.These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.

En plus de ces colonnes de période, une table temporelle contient également une référence à une autre table avec un schéma en miroir.In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. Le système utilise cette table pour stocker automatiquement la version précédente de la ligne chaque fois qu’une ligne de la table temporelle est mise à jour ou supprimée.The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. Cette table supplémentaire est appelée table d’historique. La table principale qui stocke les versions de ligne actuelles (réelles) est appelée table actuelle ou simplement table temporelle.This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. Lors de la création d’une table temporelle, les utilisateurs peuvent spécifier une table d’historique existante (qui doit être compatible avec le schéma) ou laisser le système créer une table d’historique par défaut.During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table.

Pourquoi la fonctionnalité temporelleWhy temporal

Les sources de données réelles sont dynamiques et la plupart des décisions commerciales s’appuient sur des informations que les analystes obtiennent en observant l’évolution de données.Real data sources are dynamic and more often than not business decisions rely on insights that analysts can get from data evolution. Les tables temporelles sont utilisées notamment dans les cas suivants :Use cases for temporal tables include:

  • Audit de toutes les modifications de données et analyse des données si nécessaireAuditing all data changes and performing data forensics when necessary
  • Reconstruction de l’état des données à partir d’un moment quelconque dans le passéReconstructing state of the data as of any time in the past
  • Calcul des tendances dans le tempsCalculating trends over time
  • Maintien d’une dimension à variation lente pour les applications d’aide à la décisionMaintaining a slowly changing dimension for decision support applications
  • Récupération à la suite de modifications accidentelles des données et d’erreurs d’applicationRecovering from accidental data changes and application errors

Comment fonctionnent les tables temporellesHow does temporal work

La gestion des versions d’une table est implémentée sous forme de paire de tables, une table actuelle et une table d’historique.System-versioning for a table is implemented as a pair of tables, a current table and a history table. Dans chacune de ces tables, les deux colonnes datetime2 supplémentaires suivantes permettent de définir la période de validité de chaque ligne :Within each of these tables, the following two additional datetime2 columns are used to define the period of validity for each row:

  • Colonne de début de la période : le système enregistre l’heure de début associée à la ligne de cette colonne, généralement désignée comme colonne SysStartTime.Period start column: The system records the start time for the row in this column, typically denoted as the SysStartTime column.
  • Colonne de fin de la période : le système enregistre l’heure de fin associée à la ligne de cette colonne, généralement désignée comme colonne SysEndTime.Period end column: The system records the end time for the row in this column, typically denoted as the SysEndTime column.

La table actuelle contient la valeur actuelle pour chaque ligne.The current table contains the current value for each row. La table d’historique contient la valeur précédente de chaque ligne, le cas échéant, ainsi que l’heure de début et l’heure de fin de la période pendant laquelle elle était valide.The history table contains each previous value for each row, if any, and the start time and end time for the period for which it was valid.

Temporal-HowWorksTemporal-HowWorks

L’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 dbo.Employee
(
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
  • Opérations INSERT : lors d’une opération INSERT, le système définit la valeur de la colonne SysStartTime sur l’heure de début de la transaction en cours (dans le fuseau horaire UTC) d’après l’horloge du système et il attribue à la colonne SysEndTime la valeur maximale de 9999-12-31.INSERTS: On an INSERT, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and assigns the value for the SysEndTime column to the maximum value of 9999-12-31. La ligne est alors marquée comme ouverte.This marks the row as open.
  • Opérations UPDATE : lors d’une opération UPDATE, le système stocke la valeur précédente de la ligne dans la table d’historique et il définit la valeur de la colonne SysEndTime sur l’heure de début de la transaction en cours (dans le fuseau horaire UTC) d’après l’horloge du système.UPDATES: On an UPDATE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. La ligne est alors marquée comme fermée, avec une période enregistrée pendant laquelle la ligne était valide.This marks the row as closed, with a period recorded for which the row was valid. Dans la table actuelle, la ligne est mise à jour avec la nouvelle valeur et le système définit la valeur de la colonne SysStartTime sur l’heure de début de la transaction (dans le fuseau horaire UTC) d’après l’horloge du système.In the current table, the row is updated with its new value and the system sets the value for the SysStartTime column to the begin time for the transaction (in the UTC time zone) based on the system clock. La valeur de la ligne mise à jour dans la table actuelle pour la colonne SysEndTime conserve la valeur maximale de 9999-12-31.The value for the updated row in the current table for the SysEndTime column remains the maximum value of 9999-12-31.
  • Opérations DELETE : lors d’une opération DELETE, le système stocke la valeur précédente de la ligne dans la table d’historique et il définit la valeur de la colonne SysEndTime sur l’heure de début de la transaction en cours (dans le fuseau horaire UTC) d’après l’horloge du système.DELETES: On a DELETE, the system stores the previous value of the row in the history table and sets the value for the SysEndTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock. La ligne est alors marquée comme fermée et la période pendant laquelle la ligne précédente était valide est enregistrée.This marks the row as closed, with a period recorded for which the previous row was valid. Dans la table actuelle, la ligne est supprimée.In the current table, the row is removed. Les requêtes de la table actuelle ne renvoient pas cette ligne.Queries of the current table will not return this row. Seules les requêtes qui traitent des données d’historique renvoient les données pour lesquelles une ligne est fermée.Only queries that deal with history data return data for which a row is closed.
  • Opérations MERGE : lors d’une opération MERGE, l’opération se comporte exactement comme si un maximum de trois instructions (une instruction INSERT, une instruction UPDATE et/ou une instruction DELETE) s’exécutait, selon ce qui est spécifié comme actions dans l’instruction MERGE.MERGE: On a MERGE, the operation behaves exactly as if up to three statements (an INSERT, an UPDATE, and/or a DELETE) executed, depending on what is specified as actions in the MERGE statement.

Important

Les heures enregistrées dans les colonnes datetime2 système sont basées sur l’heure de début de la transaction proprement dite.The times recorded in the system datetime2 columns are based on the begin time of the transaction itself. Par exemple, toutes les lignes insérées dans une seule transaction ont la même heure UTC enregistrée dans la colonne correspond au début de la période SYSTEM_TIME .For example, all rows inserted within a single transaction will have the same UTC time recorded in the column corresponding to the start of the SYSTEM_TIME period.

Comment interroger des données temporellesHow do I query temporal data

La clause FROM <table> de l’instruction SELECT utilise une nouvelle clause FOR SYSTEM_TIME avec cinq sous-clauses temporelles spécifiques pour interroger les données des tables actives et d’historique.The SELECT statement FROM<table> clause has a new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. Cette nouvelle syntaxe de l’instruction SELECT est prise en charge directement sur une table unique, propagée par plusieurs jointures et par des vues sur plusieurs tables temporelles.This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Temporal-QueryingTemporal-Querying

La 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 row with EmployeeID = 1000 that were active at least for a portion of period between 1st January of 2014 and 1st January 2015 (including the upper boundary):

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

Notes

FOR SYSTEM_TIME filtre les lignes dont la période de validité indique une durée égale à zéro (SysStartTime = SysEndTime).FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Ces lignes sont générées si vous effectuez plusieurs mises à jour sur la même clé primaire au sein de la même transaction.Those rows will be generated if you perform multiple updates on the same primary key within the same transaction. Dans ce cas, l’interrogation des données temporelles renvoie uniquement les versions de ligne avant les transactions et celles qui sont devenues réelles après les transactions.In that case, temporal querying surfaces only row versions before the transactions and ones that became actual after the transactions. Si vous devez inclure ces lignes dans l’analyse, interrogez la table d’historique directement.If you need to include those rows in the analysis, query the history table directly.

Dans le tableau ci-dessous, SysStartTime dans la colonne Lignes qualifiées représente la valeur figurant dans la colonne SysStartTime de la table interrogée et SysEndTime représente la valeur figurant dans la colonne SysEndTime de la même table.In the table below, SysStartTime in the Qualifying Rows column represents the value in the SysStartTime column in the table being queried and SysEndTime represents the value in the SysEndTime column in the table being queried. Pour la syntaxe complète et des exemples, consultez FROM (Transact-SQL) et Interrogation des données dans une table temporelle avec version gérée par le système.For the full syntax and for examples, see FROM (Transact-SQL) and Querying Data in a System-Versioned Temporal Table.

ExpressionExpression Lignes qualifiéesQualifying Rows DescriptionDescription
AS OF<date_time>AS OF<date_time> SysStartTime <= date_time AND SysEndTime > date_timeSysStartTime <= date_time AND SysEndTime > date_time Renvoie une table avec une ligne contenant les valeurs qui étaient réelles (actuelles) au moment spécifié dans le passé.Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. En interne, une union est effectuée entre la table temporelle et sa table d’historique. Les résultats sont filtrés de manière à renvoyer les valeurs de la ligne qui était valide au moment spécifié par le paramètre <date_time> .Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter. La valeur d’une ligne est considérée comme valide si la valeur system_start_time_column_name est inférieure ou égale à celle du paramètre <date_time> et si la valeur system_end_time_column_name est supérieure à celle du paramètre <date_time> .The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the <date_time> parameter value and the system_end_time_column_name value is greater than the <date_time> parameter value.
FROM<start_date_time>TO<end_date_time>FROM<start_date_time>TO<end_date_time> SysStartTime < end_date_time AND SysEndTime > start_date_timeSysStartTime < end_date_time AND SysEndTime > start_date_time Renvoie une table avec les valeurs de toutes les versions de ligne qui étaient actives pendant l’intervalle de temps spécifié, sans tenir compte du fait qu’elles soient devenues actives avant la valeur du paramètre <start_date_time> pour l’argument FROM ou qu’elles aient cessé d’être actives après la valeur du paramètre <end_date_time> pour l’argument TO.Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time> parameter value for the TO argument. En interne, une union est effectuée entre la table temporelle et sa table d’historique. Les résultats sont filtrés de manière à renvoyer les valeurs de toutes les versions de ligne qui étaient actives à tout moment de l’intervalle spécifié.Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Les lignes qui ont cessé d’être actives exactement à la limite inférieure définie par le point de terminaison FROM ne sont pas incluses. Les enregistrements qui sont devenus actifs exactement à la limite supérieure définie par le point de terminaison TO ne sont pas inclus non plus.Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>BETWEEN<start_date_time>AND<end_date_time> SysStartTime <= end_date_time AND SysEndTime > start_date_timeSysStartTime <= end_date_time AND SysEndTime > start_date_time Identique à la description de FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> ci-dessus, sauf que la table de lignes renvoyée inclut des lignes qui sont devenues actives sur la limite supérieure définie par le point de terminaison <end_date_time>.Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO <end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)CONTAINED IN (<start_date_time> , <end_date_time>) SysStartTime >= start_date_time AND SysEndTime <= end_date_timeSysStartTime >= start_date_time AND SysEndTime <= end_date_time Renvoie une table avec les valeurs de toutes les versions de ligne qui ont été ouvertes et fermées dans l’intervalle de temps spécifié, défini par les deux valeurs datetime de l’argument CONTAINED IN.Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Les lignes qui sont devenues actives exactement sur la limite inférieure ou qui ont cessé d’être actives exactement sur la limite supérieure sont incluses.Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALLALL Toutes les lignesAll rows Renvoie l’union de lignes appartenant à la table actuelle et à la table d’historique.Returns the union of rows that belong to the current and the history table.

Notes

Vous pouvez éventuellement masquer ces colonnes de période pour qu’elles ne soient pas retournées par les requêtes qui ne les référencent pas explicitement (scénario SELECT * FROM <table> ).Optionally, you can choose to hide these period columns such that queries that do not explicitly reference these period columns do not return these columns (the SELECT * FROM<table> scenario). Pour renvoyer une colonne masquée, incluez simplement une référence explicite à celle-ci dans la requête.To return a hidden column, simply explicitly refer to the hidden column in the query. De même, les instructions INSERT et BULK INSERT vont continuer d’agir comme si ces nouvelles colonnes de période étaient absentes (et les valeurs de la colonne seront remplies automatiquement).Similarly INSERT and BULK INSERT statements will continue as if these new period columns were not present (and the column values will be auto-populated). Pour plus d’informations sur l’utilisation de la clause HIDDEN , consultez CREATE TABLE (Transact-SQL) et ALTER TABLE (Transact-SQL).For details on using the HIDDEN clause, see CREATE TABLE (Transact-SQL) and ALTER TABLE (Transact-SQL).

Étapes suivantesNext steps