Partage via

Créer une table temporelle versionnée par le système

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Base de données Azure SQL Azure SQL Managed Instance

Il existe trois façons de créer une table temporelle avec versions gérées par le système en ce qui concerne la façon dont la table d’historique est spécifiée :

  • Table temporelle avec table de l’historique anonyme : vous spécifiez le schéma de la table actuelle et laissez le système créer une table de l’historique correspondante avec un nom généré automatiquement.

  • Table temporelle avec table de l’historique par défaut : vous pouvez spécifier le nom de schéma de la table de l’historique et le nom de la table, puis laisser le système créer une table de l’historique dans ce schéma.

  • Table temporelle avec table de l’historique définie par l’utilisateur créée au préalable : vous créez une table de l’historique adaptée à vos besoins, puis référencez cette table lors de la création de la table temporelle.

Créer une table temporelle avec une table de l’historique anonyme

La création d’une table temporelle avec une table de l’historique anonyme est une option pratique pour créer rapidement un objet, en particulier dans des environnements de test et de prototypage. C’est aussi la méthode la plus simple pour créer une table temporelle, car elle ne nécessite aucun paramètre dans la clause SYSTEM_VERSIONING. Dans l’exemple suivant, une nouvelle table est créée, avec contrôle de version du système activé, sans qu’il faille définir le nom de la table de l’historique.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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);

Notes

Une table temporelle avec versions gérées par le système doit avoir une clé primaire définie et exactement une instruction PERIOD FOR SYSTEM_TIME définie avec deux colonnes datetime2, déclarée comme GENERATED ALWAYS AS ROW START ou GENERATED ALWAYS AS ROW END.

Les colonnes PERIOD sont toujours considérées comme non nullables, même si la possibilité de valeur null n’est pas spécifiée. Si les colonnes PERIOD sont explicitement définies comme acceptant les valeurs Null, l’instruction CREATE TABLE échoue.

La table de l’historique doit toujours être alignée par schéma sur la table actuelle ou temporelle, en ce qui concerne le nombre de colonnes, les noms de colonnes, le classement et les types de données.

Une table de l’historique anonyme est créée automatiquement sur le même schéma que la table en cours ou temporelle.

Le nom de la table de l’historique anonyme a le format suivant : MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>. Le suffixe est facultatif, et il est ajouté seulement si la première partie du nom de la table n’est pas unique.

La table de l’historique est créée en tant que table rowstore. Une compression PAGE est appliquée si possible. Autrement, la table de l’historique est décompressée. Par exemple, certaines configurations de table, comme des colonnes SPARSE, n’autorisent pas la compression.

Un index cluster par défaut est créé pour la table de l’historique avec un nom généré automatiquement au format IX_<history_table_name>. L’index cluster contient les colonnes PERIOD (début, fin).

Pour créer la table actuelle comme table à mémoire optimisée, consultez Tables temporelles à système par version avec tables optimisées en mémoire.

Création d’une table temporelle avec une table de l’historique par défaut

La création d’une table temporelle avec une table de l’historique par défaut est une option pratique quand vous voulez contrôler l’affectation des noms, tout en continuant de laisser le système créer la table de l’historique avec la configuration par défaut. Dans l’exemple suivant, une nouvelle table est créée, avec le contrôle de version du système activé et le nom de la table de l’historique défini explicitement.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

Notes

La table de l’historique est créée à l’aide des règles appliquées à la création d’une table de l’historique « anonyme », les règles suivantes s’appliquant spécifiquement à la table de l’historique nommée.

  • Le nom du schéma est obligatoire pour le paramètre HISTORY_TABLE.

  • Si le schéma spécifié n’existe pas, l’instruction CREATE TABLE échoue.

  • Si la table spécifiée par le paramètre HISTORY_TABLE existe déjà, elle est validée par rapport à la table temporelle nouvellement créée sur les plans de la cohérence du schéma et de la cohérence des données temporelles. Si vous spécifiez une table de l’historique non valide, l’instruction CREATE TABLE échoue.

Création d’une table temporelle avec une table de l’historique définie par l’utilisateur

La création d’une table temporelle avec une table de l’historique définie par l’utilisateur est une option pratique pour un utilisateur désireux de spécifier une table de l’historique avec des options de stockage et différents index adaptés aux requêtes historiques. Dans l’exemple suivant, une table de l’historique définie par l’utilisateur est créée avec un schéma qui est aligné avec la table temporelle qui est créée. Pour cette table d'historique définie par l'utilisateur, un index columnstore en cluster et un index rowstore non clustérisé supplémentaire (arbre B) sont créés pour les recherches de points. Après la création de cette table d'historique définie par l'utilisateur, la table temporelle est créée en spécifiant la table d'historique définie par l'utilisateur comme table d'historique par défaut.

Remarque

De manière générale, la documentation utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT 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.DepartmentHistory));

Notes

Si vous projetez d’exécuter des requêtes analytiques sur des données historiques qui emploient des agrégats ou des fonctions de fenêtrage, la création d’un index columnstore cluster en tant qu’index primaire est une option vivement recommandée sur les plans de la compression et des performances des requêtes.

Si vous envisagez d'utiliser des tables temporelles pour l'audit des données (c'est-à-dire la recherche de modifications historiques pour une seule ligne de la table actuelle), vous devez créer une table d'historique rowstore avec un index cluster.

La table d’historique ne peut pas avoir de clé primaire, de clés étrangères, d’index uniques, de contraintes de table ou de déclencheurs. Elle ne peut pas être configurée pour la capture des changements de données, le suivi des modifications, la réplication transactionnelle ou la réplication de fusion.

Modifier une table non temporelle pour la convertir en table temporelle avec contrôle de version du système

Vous vous devez activer le contrôle de version du système sur une table non temporelle existante, par exemple, quand vous voulez migrer une solution temporelle personnalisée vers une prise en charge intégrée.

Par exemple, vous avez peut-être un ensemble de tables où le contrôle de version est implémenté avec des déclencheurs. L’utilisation d’un contrôle de version du système temporel est moins complexe et offre des d’autres avantages, notamment :

  • Historique immuable
  • Nouvelle syntaxe pour les requêtes se déplaçant dans le temps
  • Meilleures performances DML
  • Coûts de maintenance minimal

Lors de la conversion d’une table existante, envisagez d’utiliser la clause HIDDEN pour masquer les nouvelles colonnes PERIOD (les colonnes datetime2 ValidFrom et ValidTo) afin d’éviter d’attribuer des applications existantes qui ne spécifient pas explicitement les noms de colonne (par exemple SELECT * ou INSERT sans liste de colonnes) non conçues pour gérer de nouvelles colonnes.

Ajout du contrôle de version à des tables non temporelles

Si vous voulez commencer à suivre les modifications apportées à une table non temporelle contenant des données, vous devez ajouter la définition PERIOD et éventuellement fournir un nom pour la table de l’historique vide que SQL Server créé pour vous :

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

Important

La précision de DATETIME2 doit être alignée sur la précision de la table sous-jacente.

Notes

L’ajout de colonnes n’acceptant pas les valeurs Null et comportant des valeurs par défaut à une table existante contenant des données est une opération sur la taille des données pour toutes les éditions autres que SQL Server Entreprise Edition (version sur laquelle il s’agit d’une opération de métadonnées). Sur l’édition SQL Server Standard, l’ajout d’une colonne non Null à une table de l’historique volumineuse contenant des données peut être une opération coûteuse.

Les contraintes applicables aux colonnes de fin et de début de la période doivent être choisies avec soin :

  • Par défaut, la colonne de début spécifie le point dans le temps à partir duquel vous considérez que les lignes existantes sont valides. Elle ne peut pas être spécifiée sous la forme d’un point datetime dans le futur.

  • La date/heure de fin doit être spécifiée comme valeur maximale pour une précision datetime2 donnée, par exemple 9999-12-31 23:59:59 ou 9999-12-31 23:59:59.9999999.

L’ajout de PERIOD entraîne une vérification de cohérence des données de la table actuelle pour s’assurer que les valeurs existantes dans les colonnes de période sont valides.

Quand une table de l’historique existant est spécifiée lors de l’activation de SYSTEM_VERSIONING, une vérification de cohérence des données temporelles est effectuée sur les tables actuelles et de l’historique. Elle peut être ignorée si vous spécifiez DATA_CONSISTENCY_CHECK = OFF comme paramètre supplémentaire.

Migrer de tables existantes vers la prise en charge intégrée

Cet exemple montre comment migrer d’une solution basée sur des déclencheurs vers la prise en charge temporelle intégrée. Pour cet exemple, nous partons du principe que la solution personnalisée active fractionne les données actuelles et historiques en deux tables utilisateur séparées (ProjectTaskCurrent et ProjectTaskHistory).

Si votre solution utilise une table unique pour stocker les lignes réelles et historiques, vous devez fractionner les données en deux tables avant d’effectuer les étapes de migration présentées dans l’exemple suivant. Tout d’abord, supprimez le déclencheur sur la table temporelle future. Vérifiez ensuite que les colonnes PERIOD sont non-nullables.

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

Notes

Référencer des colonnes existantes dans la définition PERIOD modifie implicitement generated_always_type en AS_ROW_START et AS_ROW_END pour ces colonnes.

L’ajout de PERIOD entraîne une vérification de cohérence des données de la table actuelle pour s’assurer que les valeurs existantes dans les colonnes de période sont valides.

Nous recommandons vivement de définir SYSTEM_VERSIONING avec DATA_CONSISTENCY_CHECK = ON pour appliquer les vérifications de cohérence des données sur les données existantes.

Si les colonnes masquées sont préférables, utilisez la commande ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Contenu connexe