Comment utiliser les tables temporelles SQL Server ?

Introduction aux tables temporelles SQL Server avec versions gérées par le système pour suivre de manière automatique les version des données.

Les tables temporelles dans SQL Server, également connues sous le nom de tables versionnées par le système, sont une fonctionnalité introduite dans MS SQL Server 2016. Elles offrent un support intégré pour stocker les données historiques liées aux modifications apportées aux données d’une table. Cette fonctionnalité est bénéfique pour les besoins d’audit ou d’analyse des données commerciales, où il est crucial de comprendre l’état des données à un moment donné. Et aussi pour toutes les entreprises soumises à des rapports réglementaires réguliers avec des obligations de transparence légale.

Fonctionnement de base des tables temporelles MS SQL

Commençons par créer une table temporelle de base en Transact-SQL. Ainsi, le code T-SQL ci-dessous crée une table nommée Employee avec un versionnement système. Les colonnes SysStartTime et SysEndTime sont utilisées pour enregistrer la période de chaque ligne. Pour faire un petit récapitulatif, 3 éléments supplémentaires sont nécessaires pour créer et gérer une table temporelle SQL Server :

• SysStartTime colonne système pour marquer le début de la période, prend en charge uniquement le type de données DATETIME2.
• SysEndTime pour marquer la fin, prend en charge uniquement le type de données DATETIME2.
• Une table d’historique système, ici nommée dbo.EmployeeHistory, avec la même structure, sauf les clés.

L’instruction de création de table est spécifique

La définition de la table est spéciale, cependant, et c’est le but, insérer des données dans une table versionnée par le système est similaire à insérer des données dans n’importe quelle autre table SQL Server. En effet, les tables versionnées par le système, c’est-à-dire les tables temporelles, sont conçues pour gérer automatiquement le versionnement des données, particulièrement utile pour gérer les dimensions et les dimensions à évolution lente, appelées SCD.

CREATE TABLE [Expert-Only].dbo.Employee
(
   EmployeeId INT PRIMARY KEY,
   EmployeeName NVARCHAR(100),
   Position NVARCHAR(100),
   Salary DECIMAL(18, 2),
   SysStartTime  DATETIME2 GENERATED ALWAYS AS ROW START,
   SysEndTime    DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (
   HISTORY_TABLE = dbo.EmployeeHistory)
);

Insérer des données dans la table temporelle

Cette instruction T-SQL INSERT de base insérera une nouvelle ligne dans la table Employee, et elle mettra à jour la table d’historique associée lorsque les données changeront.

INSERT INTO [Expert-Only].dbo.Employee (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer', 80000);

Structure de la table temporelle gérée et de la table d’historique

Comme vous pouvez le voir dans la capture d’écran ci-dessous, la table Employee est maintenant versionnée par le système et la table EmployeeHistory se trouve juste derrière elle dans l’explorateur SSMS. Les deux tables ont la même structure, sauf bien sûr les clés primaires, car la table d’historique doit stocker plusieurs versions de lignes avec les mêmes ID.

Mettre à jour les données SQL dans les tables temporelles

Lorsque vous mettez à jour un enregistrement dans une table versionnée par le système, SQL Server insère automatiquement une copie de l’ancienne ligne dans la table d’historique. Ainsi, vous pouvez analyser tout changement passé pour mieux comprendre vos données. Par conséquent, après l’exécution de cette commande SQL, la table EmployeeHistory aura un enregistrement avec l’ancien salaire. Pour illustrer le concept, la requête suivante déclenche une insertion avec l’ancienne valeur de la ligne, directement dans la table d’historique.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

Supprimer des données des tables versionnées par le système

La suppression d’enregistrements dans une table versionnée par le système ne supprime pas complètement les données. Les enregistrements supprimés seront déplacés vers la table d’historique. Après l’exécution de la commande T-SQL ci-dessous, la table EmployeeHistory aura l’enregistrement supprimé.

DELETE FROM Employee
   WHERE EmployeeId = 1;

Il faut donc bien sûr être conscient de la fréquence des changements de données et aussi des volumes de données pour éviter de se retrouver avec d’énormes bases de données et sauvegardes. Vous pouvez le voir comme le même principe que la corbeille Windows, en effet, vous pouvez avoir plusieurs versions du même fichier, supprimées mais toujours dans la corbeille, jusqu’à ce que vous la vidiez.

Une solution à cela est de mettre en place un système de purge qui supprime périodiquement les anciens enregistrements de la table d’historique.

Requête T-SQL pour vérifier les données des tables temporelles

SQL Server fournit la clause FOR SYSTEM_TIME pour interroger les données d’une table versionnée par le système. Cette commande SQL renverra les données telles qu’elles étaient au moment spécifié. Vous pouvez utiliser plus de détails bien sûr pour être plus précis. Vous pouvez aller jusqu’à

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10';

SELECT *
  FROM Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 15:30:45.1234567';

Tester une table historisée gérée pour mieux comprendre

Pour effectuer un test plus approfondi, récapitulons toutes les actions effectuées, et ajoutons-en une, dans cet ordre pour voir si la table suit correctement nos modifications. Entre chaque étape, attendez quelques minutes pour une meilleure visibilité dans la table d’historique.

1. Insérer la première ligne avec un salaire de 80’000 dollars (réalisé dans la deuxième section du tutoriel)
2. Mettre à jour le salaire à 85’000 dollars
3. Supprimer l’employé de la table.
4. Insérer à nouveau le même employé, mais cette fois avec un salaire de 90’000 $ et un rôle de Developer Senior.
5. Supprimer à nouveau l’employé de la table. Non visible sur la capture d’écran mais disponible dans le code T-SQL ci-dessous.

UPDATE [Expert-Only].dbo.Employee
   SET Salary = 85000
   WHERE EmployeeId = 1;

-- *** Attendre quelques minutes *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

-- *** Attendre quelques minutes *** -- 
INSERT INTO [Expert-Only].dbo.Employee
   (EmployeeId, EmployeeName, Position, Salary)
VALUES (1, 'John Doe', 'Developer Senior', 90000);

-- *** Attendre quelques minutes *** -- 
DELETE FROM [Expert-Only].dbo.Employee
   WHERE EmployeeId = 1;

SELECT * FROM dbo.EmployeeHistory;

-- Premier insert
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 10:56:00';

-- Mise à jour à 85000 $
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:03:00';

-- Deuxième insertion en tant que Developper Senior
SELECT * FROM [Expert-Only].dbo.Employee
  FOR SYSTEM_TIME AS OF '2023-10-10 11:06:00';

Ensuite, vérifiez le résultat en utilisant SSMS pour sélectionner le contenu de la table d’historique et aussi la table Employee à des points très spécifiques dans le temps. En conséquence, la table Employee est maintenant vide. Les trois lignes archivées de la table EmployeeHistory sont disponibles en utilisant une instruction SELECT sur la table Employee elle-même à 3 moments différents.

Modifier une table temporelle avec version gérée par le système

Bien sûr, comme les modèles de données évoluent dans la base de données au fil du temps, vous devez maintenir la table d’historique lorsque la table versionnée change. Pour modifier le schéma d’une table versionnée par le système, par exemple pour ajouter une nouvelle colonne à la table Employé, suivez ces 3 étapes, dans cet ordre:

1. Désactivez d’abord la gestion des versions par le système sur la table.
2. Apportez les modifications à la table temporelle.
3. Puis réactivez la gestion des versions par le système.

Supprimer une table temporelle sans erreur

Comme il s’agit d’un type de table spécifique, pour la supprimer, vous devez également dans ce cas arrêter manuellement la version avant de supprimer la table. Pour supprimer une table avec version système sans erreur, utilisez l’option de génération de script de SSMS pour générer l’instruction complète. Comme vous pouvez le voir ci-dessous, il effectue 3 opérations:

1. Arrêtez la version.
2. Supprimez la table versionnée par le système.
3. Supprimez la table temporelle contenant l’historique.

Si vous essayez de supprimer la table sans arrêter la version, le système renvoie cette erreur:

Msg 13552, Niveau 16, État 1, Ligne 1 La suppression de la table a échoué sur la table ‘Expert-Only.dbo.Employee’ car ce n’est pas une opération prise en charge sur les tables temporelles versionnées par le système.

---

Conclusion sur la configuration des tables temporelles SQL Server

En conclusion, les Tables Temporelles SQL Server offrent une solution intégrée pour suivre les modifications des données au fil du temps. Elles peuvent être précieuses dans des situations où un audit ou une analyse des données est nécessaire. Pour des scénarios réels en entreprise, lorsque vous deviez effectuer toutes les étapes d’archivage avec du code personnalisé ou SCD, c’était complexe et lourd à maintenir. Utiliser cette fonctionnalité de table versionnée par le système la rend beaucoup plus simple à mettre en œuvre.

En comprenant comment créer, insérer, mettre à jour, supprimer et interroger des données à partir de ces tables, on peut tirer parti de cette puissante fonctionnalité dans MS SQL Server. Pour aller plus loin et découvrir d’autres cas d’utilisation pour les tables temporelles, un exemple typique et concret est de charger une table de dimension d’entrepôt de données en utilisant un package SSIS.

Importer un Fichier CSV dans une Table avec SSIS

Pour plus d’informations détaillées sur le travail avec les Tables Temporelles dans SQL Server, consultez la documentation officielle de Microsoft.