Interroger des données dans une table temporelle avec version système

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure 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 PERIOD colonnes ne sont pas masquées, leurs valeurs s’affichent dans une SELECT * requête. Si vous avez spécifié PERIOD des colonnes comme HIDDEN, leurs valeurs n’apparaissent pas dans une SELECT * requête. 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 FROM (Transact-SQL)

  • 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

FOR SYSTEM_TIMELa valeur 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. Lorsque vous utilisez un alias de table avec une table temporelle, la FOR SYSTEM_TIME clause doit être incluse entre le nom de la table temporelle et l’alias (voir Interroger pour une heure spécifique à l’aide du AS OF deuxième exemple de sous-clause ).

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 AS OF sous-clause peut être utilisée avec des littéraux constants ou des variables, afin que vous puissiez 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 AS OF une sous-clause dans les 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 instant dans le temps, car SQL Server applique AS OF la clause de manière transparente à toutes les tables temporelles qui participent à la définition de l’affichage. En outre, vous pouvez combiner des tables temporelles avec des tables non temporelles dans la même vue et AS OF s’applique uniquement aux tables temporelles. Si la vue ne fait pas référence à au moins une table temporelle, l’application de clauses d’interrogation temporelles à celle-ci échoue avec une erreur.

L’exemple de code suivant crée une vue qui joint trois tables temporelles : Department, CompanyLocationet 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 maintenant interroger la vue à l’aide de la AS OF sous-clause 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';

Vous pouvez également interroger la vue à l’aide de la AS OF sous-clause avec un fuseau horaire local et AT TIME ZONE:

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

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 uniquement des versions de ligne non actuelles, vous devez interroger directement la table d’historique pour obtenir de meilleures performances de requête. 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;

Étapes suivantes