La version finale de SQL Server 2016 est sortie récemment. C’est donc le moment de s’intéresser aux nouvelles fonctionnalités apportées par cette version. Aujourd’hui nous allons découvrir les Temporal Tables.

Les Temporal Tables permettent de conserver automatiquement un historique des données d’une table. Une table de type Temporal Table est en fait constitué de 2 tables liés. La première contient les données actuelles alors que la deuxième contient les données historiques. Tous les INSERT, UPDATE, DELETE s’effectuent sur la première table et SQL Server se débrouille pour copier les données vers la table contenant l’historique. L’intérêt est ensuite de pouvoir requêter ces données historiques simplement.

Pour l’exemple nous allons créer une table contenant les taux de change des monnaies :

CREATE TABLE dbo.ExchangeRate
(
    Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Currency nchar(3) NOT NULL,
    Rate float NOT NULL,
   
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,   
    
    PERIOD FOR SYSTEM_TIME (
        ValidFrom,
        ValidTo
    )
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ExchangeRateHistory))
GO

Pour créer la table nous créons les colonnes dont nous avons besoin puis on ajoute 2 colonnes techniquesValidFrom et ValidTo que l’on déclare comme PERIOD FOR SYSTEM_TIME. SQL Server connait ainsi les colonnes à utiliser lorsque l’on à besoin d’accéder à l’historique. On spécifie également le nom de la deuxième table ExchangeRateHistory qui servira à sauvegarder l’historique des données.

Pour ajouter ou modifier des données, il suffit d’utiliser les requêtes classiques INSERT, UPDATE, DELETE sur la table ExchangeRate. Si vous essayer de modifier la table ExchangeRateHistory vous aurez un message d’erreur.

INSERT INTO ExchangeRate(Currency, Rate)
VALUES('USD', 1)

INSERT INTO ExchangeRate(Currency, Rate)
VALUES('EUR', 1)

UPDATE ExchangeRate
SET Rate = 2
WHERE Currency = 'EUR'

UPDATE ExchangeRate
SET Rate = 1.5
WHERE Currency = 'EUR'

UPDATE ExchangeRate
SET Rate = 1.3
WHERE Currency = 'EUR'

UPDATE ExchangeRate
SET Rate = 1.4
WHERE Currency = 'EUR'

La table actuelle contient uniquement 2 lignes :

Et la table historique en contient un peu plus :

Pour l’exemple il est intéressant de modifier les dates de l’historique. Pour cela on désactive le lien entre les 2 tables avant de modifier les données, puis on le réactive à la fin :

ALTER TABLE ExchangeRate SET ( SYSTEM_VERSIONING = OFF )
GO

UPDATE ExchangeRateHistory SET ValidFrom='2015-01-01 00:00:00', ValidTo='2015-01-01 23:59:59' WHERE Rate = 1
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-02 00:00:00', ValidTo='2015-01-02 23:59:59' WHERE Rate = 2
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-03 00:00:00', ValidTo='2015-01-03 23:59:59' WHERE Rate = 1.5
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-04 00:00:00', ValidTo='2015-01-04 23:59:59' WHERE Rate = 1.3
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-05 00:00:00', ValidTo='2015-01-05 23:59:59' WHERE Rate = 1.4

ALTER TABLE ExchangeRate SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ExchangeRateHistory))
GO

Plutôt que de faire des SELECT sur la table history il faut utiliser l’opérateur FOR SYSTEM_TIME :

• FOR SYSTEM_TIME AS OF '2015-01-01'
• FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-01-05'
• FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-05'
• FOR SYSTEM_TIME CONTAINED IN ('2015-01-01', '2015-01-05')

FOR SYSTEM_TIME peut s’appliquer pour une table ou une vue. Dans le cas d’une vue, il s’applique automatiquement à toutes les Temporal Tables de la vue.

SELECT * FROM ExchangeRate
FOR SYSTEM_TIME AS OF '2015-01-02'
WHERE Currency = 'EUR'

SELECT * FROM ExchangeRate
FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-02'
WHERE Currency = 'EUR'

Pour une vue la requête s’écrit de la même façon :

SELECT * FROM MyView
FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-01'
WHERE Currency = 'EUR'

Conclusion

Les Temporal Tables permettent d’ajouter une fonctionnalité d’historisation à la base de données de manière transparente. En effet suite à l’activation de celle-ci, aucune modification des requêtes ni des applications n’est nécessaire. Pour requêter sur l’historique la syntaxe est simple FOR SYSTEM_TIME .... Bref très pratique !