Interrogation des données dans une table temporelle avec version gérée par le systèmeQuerying data in a system-versioned temporal table

S’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) et ultérieures OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed InstanceS’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance

Lorsque vous souhaitez obtenir l’état le plus récent (réel) des données d’une table temporelle, l’interrogation est exactement la même que pour une table non temporelle.When you want to get latest (actual) state of data in a temporal table, you can query completely the same way as you query non-temporal table. Si les colonnes PERIOD ne sont pas masquées, leurs valeurs apparaissent dans une requête SELECT * .If the PERIOD columns are not hidden, their values will appear in a SELECT * query. Si vous avez spécifié les colonnes PERIOD comme étant masquées, leurs valeurs n’apparaissent pas dans une requête SELECT *.If you specified PERIOD columns as hidden, their values won't appear in a SELECT * query. Lorsque les colonnes PERIOD sont masquées, référencez spécifiquement les colonnes PERIOD dans la clause SELECT pour retourner les valeurs de ces colonnes.When the PERIOD columns are hidden, reference the PERIOD columns specifically in the SELECT clause to return the values for these columns.

Pour exécuter une analyse temporelle, utilisez la nouvelle clause FOR SYSTEM_TIME avec quatre sous-clauses temporelles spécifiques pour interroger les données des tables actuelles et d’historique.To perform any type of time-based analysis, use the new FOR SYSTEM_TIME clause with four temporal-specific sub-clauses to query data across the current and history tables. Pour plus d’informations sur ces clauses, consultez Tables temporelles et FROM (Transact-SQL)For more information on these clauses, see Temporal Tables and FROM (Transact-SQL)

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

La clauseFOR SYSTEM_TIME peut être spécifiée de façon indépendante pour chaque table dans une requête.FOR SYSTEM_TIME can be specified independently for each table in a query. Elle peut être utilisée à l'intérieur d’expressions de table communes, de fonctions table incluses et de procédures stockées.It can be used inside common table expressions, table-valued functions and stored procedures. Lors de l’utilisation d’un alias de table avec une table temporelle, la clause FOR SYSTEM_TIME doit être comprise entre le nom de la table temporelle et l’alias (consultez le deuxième exemple dans « Requête d’un point précis dans le temps à l’aide de la sous-clause AS OF »).When using a table alias with a temporal tables, the FOR SYSTEM_TIME clause must included between the temporal table name and the alias (see "Query for a specific time using the AS OF sub-clause" second example, below).

Requête d’un point précis dans le temps à l'aide de la sous-clause AS OFQuery for a specific time using the AS OF sub-clause

Utilisez la sous-clause AS OF quand vous devez reconstruire l’état des données tel qu’il était à un point précis dans le temps.Use the AS OF sub-clause when you need to reconstruct state of data as it was at any specific time in the past. Vous pouvez reconstruire les données avec la précision de type datetime2 qui avait été spécifiée dans les définitions de colonne PERIOD .You can reconstruct the data with the precision of datetime2 type that was specified in PERIOD column definitions.

La sous-clause AS OF peut être utilisée avec des constantes littérales ou des variables, ce qui vous permet de spécifier de manière dynamique la condition de temps.The AS OF sub-clause clause can be used with constant literals or with variables, which allows you to dynamically specify time condition. Les valeurs fournies sont interprétées en heure UTC.The values provided are interpreted as UTC time.

Ce premier exemple retourne l'état de la table dbo.Department à partir (AS OF) d’une date spécifique dans le passé.This first example returns the state of the dbo.Department table AS OF a specific date in the past.

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

Ce second exemple compare les valeurs entre deux points dans le temps pour un sous-ensemble de lignes.This second example compares the values between two points in time for a subset of rows.

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.[SysStartTime], D.[SysStartTime],
D_1_Ago.[SysEndTime], D.[SysEndTime]
FROM [dbo].[Department] FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
JOIN [Department] AS D ON D_1_Ago.[DeptID] = [D].[DeptID]
AND D_1_Ago.[DeptID] BETWEEN 1 and 5 ;

Utilisation de vues avec la sous-clause AS OF dans des requêtes temporellesUsing views with AS-OF sub-clause in temporal queries

Les vues sont très utiles dans les scénarios nécessitant une analyse complexe à un point précis dans le temps.Using views is very useful in scenarios when complex point-in time analysis is required. Un exemple courant est la création aujourd’hui d’un rapport d'entreprise s’appuyant sur les valeurs du mois précédent.A common example is generating a business report today with the values for previous month.

En règle générale, les clients utilisent un modèle de base de données normalisé qui implique plusieurs tables avec des relations de clés étrangères.Usually, customers have a normalized database model which involves many tables with foreign key relationships. Il peut être très difficile de connaître l’état des données de ce modèle normalisé à un point précis dans le temps, car toutes les tables changent de manière indépendante, à leur propre rythme.Answering the question how data from that normalized model looked like at a point in the past can very challenging, since all tables change independently, on their own cadence.

Dans ce cas, la meilleure solution consiste à créer une vue et à appliquer la sous-clause AS OF à toute la vue.In this case, the best option is to create a view and apply the AS OF sub-clause to the entire view. 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 appliquera la clause AS OF de manière transparente à toutes les tables temporelles impliquées dans la définition de la vue.Using this approach allows you to decouple modeling of the data access layer from point-in time analysis as SQL Server will apply AS OF clause transparently to all temporal tables that participate in view definition. En outre, vous pouvez combiner des tables temporelles et non temporelles dans la même vue, et AS OF s’appliquera uniquement aux tables temporelles.Furthermore, you can combine temporal with non-temporal tables in the same view and AS OF will be applied only to temporal ones. Si la vue ne référence pas au moins une table temporelle, l’application de clauses de requêtes temporelles échoue et affiche une erreur.If view does not reference at least one temporal table, applying temporal querying clauses to it will fail with an error.

/* Create view that joins three temporal tables: Department, CompanyLocation, 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
/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF '2015-09-01 T10:00:00.7230011' ;

Rechercher des modifications sur des lignes spécifiques dans le tempsQuery for changes to specific rows over time

Les sous-clauses temporelles FROM...TO, BETWEEN...AND et CONTAINED IN sont utiles quand vous souhaitez effectuer un audit des données, autrement dit lorsque vous devez obtenir l’historique de toutes les modifications appliquées à une ligne spécifique dans la table actuelle.The temporal sub-clauses FROM...TO, BETWEEN...AND and CONTAINED IN are useful when you want to perform a data audit, i.e. when you need to get all historical changes for a specific row in the current table.

Les deux premières sous-clauses renvoient les 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 uniquement celles qui existaient dans des plages précises de la période.The first two sub-clauses return row versions that overlap with a specified period (i.e. those that started before given period and ended after it), while CONTAINED IN returns only those that existed within specified period boundaries.

Important

Nous recommandons, pour rechercher seulement les versions de ligne non actuelles, d'interroger directement la table d’historique, car elle offre les meilleures performances de requête.If you search for non-current row versions only, we recommend you query the history table directly as this will yield the best query performance. Utilisez ALL lorsque vous devez interroger des données historiques et actuelles sans aucune restriction.Use ALL when you need to query current and historical data without any restrictions.

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

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

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

Étapes suivantesNext steps