Partage via

Interroger les données dans une table temporelle avec version gérée par le système

  • Article

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

Quand vous voulez obtenir l’état (réel) le plus récent des données d’une table temporelle, vous pouvez l’interroger exactement de la même façon que vous interrogez une table non temporelle. Si les colonnes PERIOD ne sont pas masquées, leurs valeurs apparaissent dans une requête SELECT *. Si vous avez spécifié des colonnes PERIOD comme étant HIDDEN, leurs valeurs n’apparaissent pas dans une requête SELECT *. Quand les colonnes PERIOD sont masquées, vous devez référencer spécifiquement les colonnes PERIOD dans la clause SELECT pour retourner les valeurs de ces colonnes.

Pour exécuter n’importe quel type d’analyse temporelle, utilisez la nouvelle clause FOR SYSTEM_TIME avec quatre sous-clauses temporelles spécifiques pour d’interroger les données des tables actuelles et historiques. Pour plus d’informations sur ces clauses, consultez Tables temporelles et la clause FROM, plus JOIN, APPLY, PIVOT

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

La clause FOR SYSTEM_TIME peut être spécifiée de façon indépendante pour chaque table dans une requête. Elle peut être utilisée dans les expressions de table courantes, les fonctions table et les procédures stockées. Lors de l’utilisation d’un alias de table avec une table temporelle, la clause FOR SYSTEM_TIME doit être placée entre le nom de la table temporelle et l’alias (consultez le deuxième exemple ci-dessous dans Interroger un point précis dans le temps en utilisant la sous-clause AS OF).

Interroger un point précis dans le temps en utilisant la sous-clause AS OF

Utilisez la sous-clause AS OF quand vous devez reconstruire l’état des données tel qu’il était à un point spécifique dans le temps. Vous pouvez reconstruire les données avec la précision de type datetime2 qui a été spécifiée dans les définitions des colonnes PERIOD.

La sous-clause AS OF peut être utilisée avec des constantes littérales ou des variables, afin de pouvoir spécifier dynamiquement la condition de temps. Les valeurs fournies sont interprétées en heure UTC.

Ce premier exemple retourne l’état de la table dbo.Department à partir (AS OF) d’une date spécifique dans le passé.

-- State of entire table AS OF specific date in the past
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

Ce second exemple compare les valeurs entre deux points dans le temps pour un sous-ensemble de lignes.

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(DAY, -1, SYSUTCDATETIME());

-- Comparison between two points in time for subset of rows
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

Utiliser des vues avec la sous-clause AS OF dans des requêtes temporelles

Les vues sont utiles dans les scénarios nécessitant une analyse complexe à un point précis dans le temps. Un exemple courant est la création aujourd’hui d’un rapport d'entreprise s’appuyant sur les valeurs du mois précédent.

En règle générale, les clients utilisent un modèle de base de données normalisé qui implique de nombreuses tables avec des relations de clés étrangères. Connaître l’état des données de ce modèle normalisé à un point précis dans le temps peut être problématique, car toutes les tables changent de façon indépendante, à leur propre rythme.

Dans ce cas, la meilleure solution consiste à créer une vue et à appliquer la sous-clause AS OF à toute la vue. Cette approche vous permet de dissocier la modélisation de la couche d’accès aux données de l’analyse à un point précis dans le temps car SQL Server applique la clause AS OF de manière transparence à toutes les tables temporelles impliquées dans la définition de la vue. En outre, vous pouvez combiner des tables temporelles et non temporelles dans la même vue, et AS OF est appliqué seulement aux tables temporelles. Si la vue ne référence pas au moins une table temporelle, l’application de clauses de requêtes temporelles échoue avec une erreur.

L’exemple de code suivant crée une vue qui joint trois tables temporelles : Department, CompanyLocation et LocationDepartments :

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

Vous pouvez interroger la vue à l’aide de la sous-clause AS OF et d’un littéral datetime2 :

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

Rechercher des modifications sur des lignes spécifiques dans le temps

Les sous-clauses temporelles FROM ... TO, BETWEEN ... AND et CONTAINED IN sont utiles quand vous devez obtenir l’historique de toutes les modifications appliquées à une ligne spécifique dans la table actuelle (ceci s’appelle aussi « audit des données »).

Les deux premières sous-clauses retournent des versions de ligne qui se chevauchent sur une période donnée (c’est-à-dire celles qui ont démarré avant une certaine période et qui se sont terminées après celle-ci), tandis que CONTAINED IN retourne seulement celles qui existaient dans les limites de la période spécifiée.

Si vous recherchez seulement les versions de ligne non actuelles, vous devez interroger directement la table d’historique pour de meilleures performances pour les requêtes. Utilisez ALL quand vous devez interroger des données historiques et actuelles sans aucune restriction.

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

Contenu connexe