Microsoft SQL Server
Tables Temporelles

Remarques

CREATE Tables Temporelles

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

INSERTS: sur un INSERT , le système définit la valeur de la colonne ValidFrom sur l'heure de début de la transaction en cours (dans le fuseau horaire UTC) en fonction de l'horloge système et affecte la valeur de la colonne ValidTo à la valeur maximale de 9999- 12-31. Cela marque la ligne comme ouverte.

MISES À JOUR: Sur un UPDATE, le système stocke la valeur précédente de la ligne dans la table d'historique et définit la valeur de la colonne validTo au temps de début de la transaction en cours (dans le temps UTC zone) en fonction de l'horloge système. Cela marque la ligne comme fermée, avec une période enregistrée pour laquelle la ligne était valide. Dans la table en cours, la ligne est mise à jour avec sa nouvelle valeur et le système définit la valeur de la colonne ValidFrom sur l'heure de début de la transaction (dans le fuseau horaire UTC) en fonction de l'horloge système. La valeur de la ligne mise à jour dans la table en cours pour la colonne ValidTo reste la valeur maximale de 9999-12-31.

DELETES : sur un DELETE , le système stocke la valeur précédente de la ligne dans la table d'historique et définit la valeur de la colonne ValidTo sur l'heure de début de la transaction en cours (dans le fuseau horaire UTC) en fonction de l'horloge système. Cela marque la ligne comme fermée, avec une période enregistrée pour laquelle la ligne précédente était valide. Dans la table en cours, la ligne est supprimée. Les requêtes de la table en cours ne renverront pas cette ligne. Seules les requêtes qui traitent des données d'historique renvoient des données pour lesquelles une ligne est fermée.

FUSION : sur un MERGE , l'opération se comporte exactement comme si jusqu'à trois instructions (un INSERT , un UPDATE et / ou un DELETE ) étaient exécutées, en fonction des actions spécifiées dans l'instruction MERGE .

Conseil: Les heures enregistrées dans les colonnes système datetime2 sont basées sur l'heure de début de la transaction elle-même. Par exemple, toutes les lignes insérées dans une seule transaction auront la même heure UTC enregistrée dans la colonne correspondant au début de la période SYSTEM_TIME .

Comment interroger des données temporelles?

SELECT * FROM Employee   
    FOR SYSTEM_TIME    
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000'   
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;  

Renvoie la valeur réelle spécifiée dans le temps (FOR SYSTEM_TIME AS OF )

Renvoie une table avec des lignes contenant les valeurs réelles (actuelles) au moment spécifié dans le passé.

SELECT * FROM Employee   
    FOR SYSTEM_TIME AS  OF '2016-08-06 08:32:37.91'

POUR SYSTEM_TIME ENTRE ET

Comme ci-dessus dans la description FOR SYSTEM_TIME FROM <start_date_time> TO <end_date_time>, sauf que la table de lignes renvoyée comprend des lignes devenues actives sur la limite supérieure définie par le noeud final <end_date_time>.

SELECT * FROM Employee   
    FOR SYSTEM_TIME BETWEEN  '2015-01-01' AND '2015-12-31' 

POUR SYSTEM_TIME DE À

Renvoie une table avec les valeurs de toutes les versions de ligne actives dans la plage de temps spécifiée, qu'elles aient ou non commencé avant la valeur du paramètre <start_date_time> pour l'argument FROM ou qu'elles aient cessé d'être actives après la valeur du paramètre <end_date_time> TO argument. En interne, une union est effectuée entre la table temporelle et sa table d'historique et les résultats sont filtrés pour renvoyer les valeurs de toutes les versions de lignes actives à tout moment au cours de la période spécifiée. Les lignes devenues actives exactement sur la limite inférieure définie par le noeud final FROM sont incluses et les enregistrements devenus actifs exactement sur la limite supérieure définie par le noeud final TO ne sont pas inclus.

SELECT * FROM Employee   
    FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-12-31' 

POUR SYSTEM_TIME CONTENU DANS ( , )

Renvoie une table avec les valeurs de toutes les versions de lignes ouvertes et fermées dans la plage de temps spécifiée définie par les deux valeurs datetime pour l'argument CONTAINED IN. Les lignes devenues actives exactement à la limite inférieure ou cessant d'être actives exactement à la limite supérieure sont incluses.

SELECT * FROM Employee
    FOR SYSTEM_TIME CONTAINED IN ('2015-04-01', '2015-09-25')   

POUR SYSTEM_TIME ALL

Renvoie l'union des lignes qui appartiennent à la table courante et à la table d'historique.

SELECT * FROM Employee
    FOR SYSTEM_TIME ALL

Création d'une table temporelle versionnée par le système et optimisée pour la mémoire et nettoyage de la table d'historique SQL Server

La création d'une table temporelle avec une table d'historique par défaut est une option pratique lorsque vous souhaitez contrôler la dénomination et que vous comptez toujours sur le système pour créer la table d'historique avec la configuration par défaut. Dans l'exemple ci-dessous, une nouvelle table temporelle optimisée en fonction de la mémoire du système est associée à une nouvelle table d'historique sur disque.

CREATE SCHEMA History  
GO  
CREATE TABLE dbo.Department   
(  
    DepartmentNumber char(10) NOT NULL PRIMARY KEY NONCLUSTERED,   
    DepartmentName varchar(50) NOT NULL,   
    ManagerID int  NULL,   
    ParentDepartmentNumber char(10) NULL,   
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,   
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,     
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)     
)  
WITH   
    (  
        MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
        SYSTEM_VERSIONING = ON ( HISTORY_TABLE = History.DepartmentHistory )   
    );  

Nettoyage de la table d'historique SQL Server Au fil du temps, la table d'historique peut augmenter considérablement. Étant donné que l'insertion, la mise à jour ou la suppression des données de la table d'historique ne sont pas autorisées, la seule façon de nettoyer la table d'historique est de désactiver le contrôle de version du système:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = OFF); ALLER

Supprimez les données inutiles de la table d'historique:

    DELETE FROM dbo.EmployeeHistory

WHERE EndTime <= '2017-01-26 14:00:29';

puis réactivez le contrôle de version du système:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo]. [EmployeeHistory], DATA_CONSISTENCY_CHECK = ON));

Le nettoyage de la table d'historique dans les bases de données SQL Azure est un peu différent, car les bases de données SQL Azure ont une prise en charge intégrée pour le nettoyage de la table d'historique. Tout d'abord, le nettoyage de la conservation de l'historique temporel doit être activé au niveau de la base de données:

ALTER DATABASE CURRENT

SET TEMPORAL_HISTORY_RETENTION SUR GO

Ensuite, définissez la période de rétention par table:

ALTER TABLE dbo.Employee

SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 90 JOURS));

Cela supprimera toutes les données de la table d'historique de plus de 90 jours. Les bases de données SQL Server 2016 sur site ne prennent pas en charge TEMPORAL_HISTORY_RETENTION et HISTORY_RETENTION_PERIOD et l'une des deux requêtes ci-dessus est exécutée sur les bases de données SQL Server 2016 sur site. Les erreurs suivantes se produisent.

Pour TEMPORAL_HISTORY_RETENTION, l'erreur sera:

Msg 102, Level 15, State 6, Line 34

Syntaxe incorrecte près de 'TEMPORAL_HISTORY_RETENTION'.

Pour HISTORY_RETENTION_PERIOD, l'erreur sera:

Msg 102, Level 15, State 1, Line 39

Syntaxe incorrecte près de 'HISTORY_RETENTION_PERIOD'.