Comment utiliser la table temporelle SQL Server

Les tables temporelles, également connues sous le nom de tables par version système, sont des tables qui vous permettent de suivre et de conserver l'historique des données sur un tableau spécifique. À l'aide de tables temporelles, vous pouvez suivre l'historique des modifications apportées aux données dans un tableau.

Cet article mettra une base pour créer, travailler avec et utiliser des tables temporelles dans SQL Server.

Des tables versées système ont été introduites dans la norme ANSI SQL 2011 et ont été disponibles en fonction de SQL Server 2016 et au-dessus.

Contrairement à un tableau normal qui ne peut afficher et travailler avec les données actuelles, les tables temporelles vous permettent de visualiser et de travailler même avec des données précédemment supprimées. Comme mentionné, cela est possible en raison de la capacité d'un tableau temporel à suivre les modifications apportées aux données d'un tableau.

Le tableau contient deux colonnes clés: Sysstarttime et Sysendtime. Ces deux colonnes sont utilisées pour définir les données existantes et précédentes pour chaque enregistrement dans un tableau. Vous pouvez utiliser des intervalles de temps spécifiques pour voir comment les données d'un tableau ont changé.

Créer une table temporelle

Avant de pouvoir créer une table temporelle, elle doit répondre aux exigences suivantes:

1. Une table temporelle doit contenir une contrainte de clé primaire définie.
2. Il doit contenir deux colonnes pour enregistrer la date de départ et de fin. Ces colonnes doivent être de type de données DateTime2. Les colonnes doivent être déclarées toujours générées comme démarrage / fin de ligne.
3. SQL Server suppose que les deux colonnes ne sont pas nullables. Par conséquent, l'instruction Create Table échoue si la requête essaie de définir des colonnes qui sont nullables.
4. SQL Server génère automatiquement un tableau d'historique en utilisant un schéma similaire à la table temporelle.
5. Vous ne pouvez pas utiliser au lieu de déclencheurs dans un tableau des versions système.
6. Le tableau d'historique ne doit contenir aucune contrainte.
7. Vous ne pouvez pas modifier les données dans le tableau historique.
8. Des instructions, telles que l'insertion et la mise à jour, ne peuvent pas référencer les colonnes de période.
9. La table d'historique est créée comme un tableau d'historique des lignes et la compression de la page est appliquée si vous appliquez. Sinon, la table n'est pas compressée.
10. SQL Server générera automatiquement un index en cluster pour la table d'historique.

Comment créer une table temporelle: T-SQL

Regardons une simple démonstration de création d'une table temporelle. Considérez l'exemple de requête ci-dessous:

Créer la table DBO.my_temporal_table (
id int,
fname varchar (50),
Email Varchar (255),
Département Varchar (50),
Contrainte PK Clé primaire (ID),
Sysstarttime DateTime2 généré toujours comme la ligne ne commence pas nul,
Sysendtime datetime2 généré toujours comme la ligne ne pas nul,
période pour system_time (sysstarttime, sysendtime)) avec (system_versioning = on);

Une fois que nous avons exécuté la requête ci-dessus, le serveur SQL créera la table avec le nom spécifié.

Dans le SQL Server Management Studio, vous pouvez afficher une table système versée en étendant l'option Tables dans votre base de données cible:

Notez que le serveur SQL génère automatiquement une table d'historique avec un schéma similaire à celle du tableau des versions système. Cependant, faites attention aux colonnes de la table d'histoire. Notez qu'ils n'ont aucune contrainte.

Considérez l'image illustrée ci-dessous:

Comme vous le verrez, SQL Server génère une table d'historique sous un nom suivant un format spécifique. Pour définir un nom personnalisé pour votre table d'historique, spécifiez-le dans l'instruction Créer la table comme indiqué:

---
période pour system_time (sysstarttime, sysendtime)) avec (system_versioning = on, history_table = mytemporal_tablehistory);
---

Ensuite, si vous développez l'option Indexes pour le tableau historique, vous remarquez que SQL Server a généré automatiquement un index en cluster:

En utilisant des tables temporelles

Testons la fonctionnalité des tables temporelles en insérant quelques enregistrements dans le tableau. Considérez l'exemple de requête ci-dessous:

Insérer dans my_temporal_table (id, fname, e-mail, département)
Valeurs (1, «John Davis», «Davis @ Keep.mail ',' front-end '),
(2, «Ruby Raw», «Rubyraw @ info.io ',' base de données '),
(3, «Scott Turner», «Turner @ Mail.com ',' complet "),
(4, 'Alice Jensen', 'Alice.jensen @ hotmail.com ',' Contrôle de version '),
(5, «Peter Green», «vert @ p.tv ',' backend ');

Une fois que les exemples de données ont été insérés dans le tableau, nous pouvons les interroger comme:

SELECT * FROM MY_TEMPORAL_TABLE;

Vous devriez obtenir une sortie près de celle indiquée ci-dessous comme

Pour comprendre le fonctionnement du tableau des versions système, supprimons et mettons à jour les lignes dans le tableau:

Supprimer de my_temporal_table où département = 'base de données';
Mettre à jour my_temporal_table set fname = 'John m' où id = 5;

Ensuite, interrogez les données dans le tableau principal:

SELECT * FROM MY_TEMPORAL_TABLE;

Si vous interrogez la table d'histoire, vous devriez voir l'ancienne version des données avec les horodatages corrects.

Conclusion

Ce guide a couvert le concept de table temporelle ou du système dans SQL Server. En utilisant ce guide, vous serez en mesure de suivre l'historique de vos données à l'aide de tables temporelles SQL Server. Nous espérons que vous avez trouvé cet article utile. Découvrez plus d'articles sur les conseils pour les conseils et les tutoriels.