Historique temporel SQL Server: comprendre AS OF

Quand un prix passe de 10 à 12, une table ordinaire conserve seulement la nouvelle valeur, sauf historique applicatif. Une table temporelle versionnée par le système préserve automatiquement les versions précédentes. C'est utile pour enquêter, mais toutes les questions historiques ne sont pas identiques.

Distinguez valeur enregistrée selon le temps système, date d'effet voulue par le métier et personne ayant autorisé la modification. L'historique temporel répond directement à la première. Les autres exigent des informations et un processus supplémentaires.

Observer présent et passé

Cet exemple SQL Server 2016 ou ultérieur crée des tables permanentes de pratique. Exécutez-le en autocommit sans transaction extérieure.

-- Use a disposable database, autocommit, and no enclosing transaction.
IF @@TRANCOUNT <> 0 THROW 50001, 'Use a separate practice connection.', 1;
CREATE TABLE dbo.PriceTemporalDemo (
    ProductId int NOT NULL PRIMARY KEY,
    Price decimal(12,2) NOT NULL,
    ValidFrom datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.PriceTemporalDemoHistory
));
INSERT dbo.PriceTemporalDemo(ProductId, Price) VALUES (1, 10.00);
DECLARE @BeforeChange datetime2(7) = SYSUTCDATETIME();
WAITFOR DELAY '00:00:01';
UPDATE dbo.PriceTemporalDemo SET Price = 12.00 WHERE ProductId = 1;
SELECT ProductId, Price FROM dbo.PriceTemporalDemo WHERE ProductId = 1;
SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.PriceTemporalDemo FOR SYSTEM_TIME AS OF @BeforeChange
WHERE ProductId = 1;

La requête actuelle retourne 12,00; AS OF @BeforeChange retourne 10,00. La syntaxe temporelle recherche les versions pertinentes dans les données actuelles et historiques sans union manuelle.

La période utilise datetime2 en UTC. AS OF sélectionne un début inférieur ou égal à l'instant demandé et une fin strictement supérieure. La borne haute est exclue. Fournissez un paramètre UTC correctement converti, pas une heure locale avec un décalage supposé.

La pause ne sert qu'à séparer les instants de démonstration. Elle n'appartient pas à une solution de production. Sans séparation, un essai rapide peut rendre les frontières difficiles à distinguer, surtout avec une précision modifiée.

Cette requête affiche les intervalles disponibles.

SELECT ProductId, Price, ValidFrom, ValidTo
FROM dbo.PriceTemporalDemo FOR SYSTEM_TIME ALL
WHERE ProductId = 1
ORDER BY ValidFrom, ValidTo;

Une mise à jour peut créer une version même si la valeur ne change pas. Évitez les écritures inutiles quand elles gonflent l'historique, sans supprimer des changements réellement significatifs pour économiser de l'espace.

Comprendre l'horloge transactionnelle

Les frontières utilisent le début de transaction, pas sa validation. Une transaction longue peut créer une version dont le début système précède sa première visibilité comme donnée validée pour une autre connexion. AS OF suit ces règles; il ne reproduit pas exactement la perception de chaque lecteur concurrent.

Plusieurs modifications de la même ligne dans une transaction peuvent produire des versions de durée nulle. Les clauses temporelles excluent ces versions; une lecture directe de la table historique peut montrer des lignes absentes de FOR SYSTEM_TIME. Un état intermédiaire manquant ne signifie donc pas nécessairement perte.

Si un prix saisi aujourd'hui doit s'appliquer le mois prochain, conservez une date ou période métier séparée. Ne forcez pas les colonnes système à représenter cette planification. Une correction rétroactive doit également distinguer la date d'effet et le moment où la base a appris le fait.

Un même AS OF sur plusieurs tables temporelles facilite les jointures historiques. Vérifiez cependant les tables non temporelles ajoutées. Une ancienne commande jointe au nom actuel d'une catégorie mutable produit un mélange d'époques.

Exploiter l'historique comme de vraies données

Estimez la croissance avec la fréquence de modification et la largeur des lignes, pas seulement leur nombre actuel. Une petite table très modifiée peut accumuler beaucoup d'historique. Indexez le véritable usage: chercher toutes les versions d'un produit diffère d'une reconstruction globale à un instant.

Rétention et nettoyage doivent être décidés explicitement. Un rapport ne peut retrouver des versions déjà supprimées. Documentez l'horizon disponible et surveillez le mécanisme de nettoyage adapté à la version déployée.

Le temporel ne remplace ni sauvegarde ni audit immuable. Il n'enregistre pas automatiquement acteur et motif métier, et une maintenance privilégiée peut changer sa configuration. Stockez les attributions nécessaires séparément avec leurs autorisations.

Prévoyez les changements de schéma sur table actuelle et historique. Désactiver SYSTEM_VERSIONING ouvre une période sans capture automatique. Lors de la réactivation, rattachez explicitement la bonne table historique au lieu d'en créer accidentellement une nouvelle.

Testez mise à jour, suppression, transaction longue, modifications multiples et instants exactement aux frontières. Le résultat doit permettre de distinguer ce que le système a enregistré de ce que le métier voulait exprimer.

Références techniques: Microsoft Learn: Query temporal data · Microsoft Learn: Temporal considerations.