Tables temporelles

  • Article
  • 9 minutes de lecture

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Les tables temporelles (également appelées tables temporelles avec versions gérées par le système) sont une fonctionnalité des bases de données qui fournit une prise en charge intégrée pour la livraison d’informations sur les données stockées dans la table à n’importe quel point dans le temps, et non pas seulement les données qui sont correctes au moment actuel.

Vous pouvez Bien démarrer avec les Tables temporelles avec versions gérées par le système et passer en revue les Scénarios d’utilisation des tables temporelles.

Qu’est-ce qu’une table temporelle avec version gérée par le système ?

Une table temporelle avec versions gérées par le système est un type de table utilisateur conçu pour conserver un historique complet des modifications apportées aux données afin de permettre l’analyse à un point dans le temps. Ce type de table temporelle est appelée table temporelle versionnée par le système, car la période de validité de chaque ligne est gérée par le système (à avoir, le moteur de base de données).

Chaque table temporelle contient deux colonnes définies explicitement, chacune d’elles contenant un type de données datetime2 . Ces colonnes sont appelées colonnes de période. Ces colonnes de période sont utilisées exclusivement par le système pour enregistrer la période de validité pour chaque ligne chaque fois qu’une ligne est modifiée. La table principale qui stocke les données actuelles est appelée table actuelle ou simplement table temporelle.

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, appelée table d’historique. Le système utilise la table d’historique 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. Lors de la création d’une table temporelle, les utilisateurs peuvent spécifier une table d’historique existante (qui doit être conforme au schéma) ou laisser le système créer une table d’historique par défaut.

Pourquoi la fonctionnalité temporelle ?

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. Les tables temporelles sont utilisées notamment dans les cas suivants :

  • Audit de toutes les modifications de données et analyse des données si nécessaire
  • Reconstruction de l’état des données à partir d’un moment quelconque dans le passé
  • Calcul des tendances dans le temps
  • Maintien d’une dimension à variation lente pour les applications d’aide à la décision
  • Récupération à la suite de modifications accidentelles des données et d’erreurs d’application

Fonctionnement des tables temporelles

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. Dans chacune de ces tables, deux colonnes datetime2 supplémentaires sont utilisées pour définir la période de validité de chaque ligne :

  • 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 ValidFrom.
  • 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 ValidTo.

La table actuelle contient la valeur actuelle pour chaque ligne. La table d’historique contient chaque valeur précédente (la version précédente) pour 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 a été valide.

Diagramme illustrant le fonctionnement d’une table temporelle.

Le script suivant illustre un scénario avec des informations sur des employés :

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));

Pour plus d’informations, consultez Création d’une table temporelle avec versions gérées par le système.

  • INSERTIONS : le système définit la valeur de la colonne ValidFrom 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 affecte à la colonne ValidTo la valeur maximale de 9999-12-31. La ligne est alors marquée comme ouverte.
  • MISE À JOUR : le système stocke la valeur précédente de la ligne dans la table d’historique et définit la valeur de la colonne ValidTo sur la date/heure de début de la transaction en cours (dans le fuseau horaire UTC) d’après l’horloge système. La ligne est alors marquée comme fermée, avec une période enregistrée pendant laquelle la ligne était valide. Dans la table actuelle, la ligne est mise à jour avec la nouvelle valeur et le système définit la valeur de la colonne ValidFrom sur l’heure de début de la transaction (dans le fuseau horaire UTC) d’après l’horloge du système. La valeur de la ligne mise à jour dans la table actuelle pour la colonne ValidTo conserve la valeur maximale de 9999-12-31.
  • SUPPRESSIONS : le système stocke la valeur précédente de la ligne dans la table d’historique et définit la valeur de la colonne ValidTo sur la date/heure de début de la transaction en cours (dans le fuseau horaire UTC) d’après l’horloge système. 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. Dans la table actuelle, la ligne est supprimée. Les requêtes de la table actuelle ne vont pas retourner cette ligne. Seules les requêtes qui traitent des données d’historique renvoient les données pour lesquelles une ligne est fermée.
  • FUSION : 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écutaient, selon ce qui est spécifié comme actions dans l’instruction MERGE.

Important

Les dates/heures enregistrées dans les colonnes datetime2 du système sont basées sur la date/heure de début de la transaction proprement dite. 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 .

Interrogation de données temporelles

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. 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.

Quand vous interrogez en utilisant la clause FOR SYSTEM_TIME avec une des cinq sous-clauses, les données d’historique de la table temporelle seront incluses, comme illustré dans l’image suivante.

Diagramme montrant le fonctionnement de l’interrogation de données temporelles

La requête suivante recherche les versions de ligne pour un employé avec la condition de filtre WHERE EmployeeID = 1000, qui ont été actives pendant au moins une partie de la période comprise entre le 1er janvier 2021 et le 1er janvier 2022 (limite supérieure comprise) :

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

Notes

FOR SYSTEM_TIME exclut les lignes qui ont une période de validité d’une durée égale à zéro (ValidFrom = ValidTo).

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. Dans ce cas, l’interrogation des données temporelles retourne seulement les versions de ligne avant les transactions et les lignes actuelles après les transactions.

Si vous devez inclure ces lignes dans l’analyse, interrogez la table d’historique directement.

Dans le tableau ci-dessous, ValidFrom dans la colonne Qualifying Rows représente la valeur de la colonne ValidFrom dans la table interrogée, et ValidTo représente la valeur de la colonne ValidTo dans la même table. Pour la syntaxe complète et des exemples, consultez (Transact-SQL) et Interrogation des données dans une table temporelle avec version gérée par le système.

Expression Lignes qualifiées Notes
AS OFdate_time ValidFrom<= date_time AND ValidTo>date_time Retourne une table avec des lignes contenant les valeurs qui étaient actuelles au moment spécifié dans le passé. En interne, une union est effectuée entre la table temporelle et sa table d’historique, et les résultats sont filtrés pour retourner les valeurs de la ligne qui était valide au point dans le temps spécifié par le paramètre de date_heure. La valeur d’une ligne est considérée comme valide si system_start_time_column_name a une valeur inférieure ou égale à celle du paramètre de date_heure et si la valeur de system_end_time_column_name a une valeur supérieure à celle du paramètre de date_heure.
FROMstart_date_timeTOend_date_time ValidFrom<end_date_time AND ValidTo>start_date_time Retourne une table avec les valeurs pour toutes les versions de ligne qui étaient actives pendant l’intervalle de temps spécifié, sans tenir compte du fait qu’elles soient ou non devenues actives avant la valeur du paramètre date_heure_début pour l’argument FROM ou qu’elles aient cessé d’être actives après la valeur du paramètre date_heure_fin pour l’argument TO. 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é. 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, et 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.
BETWEENstart_date_timeANDend_date_time ValidFrom<= end_date_time AND ValidTo>start_date_time Identique à la description de FOR SYSTEM_TIME FROMstart_date_timeTOend_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.
CONTAINED IN (date_heure_début, date_heure_fin) ValidFrom>= start_date_time AND ValidTo<= end_date_time Retourne une table avec les valeurs pour 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 pour la période de l’argument CONTAINED IN. 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.
ALL Toutes les lignes Renvoie l’union de lignes appartenant à la table actuelle et à la table d’historique.

Masquer les colonnes de période

Vous pouvez choisir de masquer les colonnes de période, de telle sorte que les requêtes qui ne les référencent pas explicitement ne retournent pas ces colonnes (par exemple lors de l’exécution de SELECT * FROM <table>).

Pour retourner une colonne masquée, vous devez la référencer explicitement dans la requête. De même, les instructions INSERT et BULK INSERT vont continuer d’agir comme si ces nouvelles colonnes de période n’étaient pas présentes (et les valeurs de la colonne seront remplies automatiquement).

Pour plus d’informations sur l’utilisation de la clause HIDDEN, consultez CREATE TABLE et ALTER TABLE.

Exemples

ASP.NET

Examinez cette application web ASP.NET Core pour découvrir comment créer une application temporelle en utilisant des tables temporelles.

Télécharger l’exemple de base de données Adventure Works

Vous pouvez télécharger la base de données AdventureWorks pour SQL Server, qui comprend des fonctionnalités de table temporelle.

Voir aussi

Étapes suivantes