Tables temporelles

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Base de données Azure SQL 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.

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 la 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 la 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 en savoir plus, reportez-vous àCréer 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 système et affecte la valeur pour la colonne ValidTo à la valeur maximale de 9999-12-31. La ligne est alors marquée comme ouverte.

  • Mises à 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 l’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 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 l’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 retournent pas 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.

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 contiennent la même heure UTC enregistrée dans la colonne correspondant au début de la période SYSTEM_TIME.

Lorsque vous exécutez des requêtes de modification de données sur une table temporelle, le moteur de base de données ajoute une ligne à la table d'historique, même si aucune valeur de colonne ne change.

Interrogation de données temporelles

L’instruction SELECT ... FROM <table> contient une nouvelle clause FOR SYSTEM_TIME, avec cinq sous-clauses temporelles spécifiques pour interroger les données dans les tables actuelles et historiques. Cette nouvelle syntaxe de l’instruction SELECT est directement prise en charge 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 historiques 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;

FOR SYSTEM_TIME exclut les lignes avec 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 la table ci-dessous, ValidFrom dans la colonne des lignes admissibles 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 table interrogée. Pour la syntaxe complète et des exemples, consultez clause FROM plus JOIN, APPLY, PIVOT, et Interroger les données d'une table temporelle avec version gérée par le système.

Expression Lignes qualifiées Remarque
AS OF date_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. Les résultats sont filtrés pour retourner les valeurs de la ligne qui était valide au moment spécifié par le paramètre date_time. 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 date_time et si system_end_time_column_name a une valeur supérieure à celle du paramètre date_time.
FROM start_date_time TO end_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 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. 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 de temps spécifié. Les lignes qui ont cessé d’être actives exactement à la délimitation inférieure définie par le point de terminaison FROM ne sont pas incluses, et les enregistrements qui sont devenus actifs exactement à la délimitation supérieure définie par le point de terminaison TO ne sont pas inclus non plus.
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time Identique à la description précédente dans FOR SYSTEM_TIME FROM start_date_timeend_date_time TO end_date_time, sauf que les tables de lignes retournées comprennent des lignes qui sont devenues actives sur la délimitation supérieure définie par le point de terminaison end_date_time.
CONTAINED IN (start_date_time, end_date_time) 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 de période pour 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

Contenu connexe