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_TIME
La 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
, 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 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
- Tables temporelles
- FROM (Transact-SQL)
- AT TIME ZONE (Transact-SQL)
- Créer une table temporelle avec version système
- Modifier des données dans une table temporelle avec version système
- Modifier le schéma d’une table temporelle avec version système
- Arrêter le contrôle de version système sur une table temporelle avec version système
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : tout au long de 2024, nous allons éliminer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d'informations, consultez :Envoyer et afficher des commentaires pour