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

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure 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éation d’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 également le moyen le plus simple de créer une table temporelle, car il ne nécessite aucun paramètre dans SYSTEM_VERSIONING la clause. Dans l’exemple suivant, une table est créée avec le contrôle de version système activé sans définir le nom de la table d’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 version système doit avoir une clé primaire définie et en avoir exactement une PERIOD FOR SYSTEM_TIME définie avec deux colonnes datetime2 , déclarées comme GENERATED ALWAYS AS ROW START ou GENERATED ALWAYS AS ROW END.

  • Les PERIOD colonnes sont toujours supposées être non nullables, même si la possibilité de null n’est pas spécifiée. Si les PERIOD colonnes sont explicitement définies comme pouvant être null, l’instruction CREATE TABLE échoue.

  • La table d’historique doit toujours être alignée sur le schéma de 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_<ID_objet_table_temporelle_actuelle>_ [suffixe]. Le suffixe est facultatif et est ajouté uniquement 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. La compression de page est appliquée si possible, sinon la table d’historique n’est pas 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_<nom_table_historique>. L’index cluster contient les PERIOD colonnes (fin, début).

  • Pour créer la table actuelle comme table optimisée en mémoire, 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 table est créée avec le contrôle de version système activé avec le nom de la table d’historique explicitement défini.

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 HISTORY_TABLE paramètre.
  • Si le schéma spécifié n’existe pas, l’instruction CREATE TABLE échoue.
  • Si la table spécifiée par le HISTORY_TABLE paramètre existe déjà, elle est validée par rapport à la table temporelle nouvellement créée en termes de cohérence de schéma et de cohérence des données temporelles. Si vous spécifiez une table d’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 d’historique définie par l’utilisateur est créée avec un schéma aligné sur la table temporelle créée. Pour cette table d’historique définie par l’utilisateur, un index columnstore cluster et un index rowstore (arborescence B+) non cluster supplémentaire sont créés pour les recherches de points. Une fois cette table de l’historique définie par l’utilisateur créée, la table temporelle avec contrôle de version du système est créée en spécifiant la table de l’historique définie par l’utilisateur en tant que la table de l’historique par défaut.

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 le cas d’usage principal est l’audit de données (c’est-à-dire la recherche des modifications historiques pour une seule ligne de la table actuelle), un bon choix est de 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 données modifiées, le suivi des modifications 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 pouvez activer le contrôle de version système sur une table non temporelle existante, par exemple lorsque vous souhaitez 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 HIDDEN clause pour masquer les nouvelles PERIOD colonnes (les colonnes ValidFromdatetime2 et ValidTo) afin d’éviter d’affecter les applications existantes qui ne spécifient pas explicitement les noms de colonnes (par exemple, SELECT * ou INSERT sans liste de colonnes) ne sont pas conçues pour gérer les nouvelles colonnes.

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

Si vous souhaitez commencer à suivre les modifications d’une table non temporelle qui contient les données, vous devez ajouter la PERIOD définition et éventuellement fournir un nom pour la table d’historique vide que SQL Server crée 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 pour DATETIME2 doit s’aligner sur la précision de la table sous-jacente. Consultez les remarques suivantes.

Notes

  • L’ajout de colonnes non nullables avec des valeurs par défaut à une table existante avec des données est une opération de taille de données sur toutes les éditions autres que SQL Server Entreprise édition (sur laquelle il s’agit d’une opération de métadonnées). Avec une grande table d’historique existante avec des données sur SQL Server Standard édition, l’ajout d’une colonne non null 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 PERIOD effectue une case activée de cohérence des données sur la table active pour s’assurer que les valeurs existantes pour les colonnes de période sont valides.
  • Lorsqu’une table d’historique existante est spécifiée lors de l’activation SYSTEM_VERSIONINGde , une case activée de cohérence des données est effectuée sur la table actuelle et la table d’historique. Il peut être ignoré 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 PERIOD colonnes ne sont pas 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

  • Le référencement de colonnes existantes dans PERIOD la définition change generated_always_type implicitement vers AS_ROW_START et AS_ROW_END pour ces colonnes.
  • L’ajout PERIOD effectue une case activée de cohérence des données sur la table active pour s’assurer que les valeurs existantes pour les colonnes de période sont valides
  • Il est vivement recommandé de définir SYSTEM_VERSIONING avec DATA_CONSISTENCY_CHECK = ON pour appliquer des 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;.

Étapes suivantes