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

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Vous pouvez créer une table temporelle version système de trois manières, selon la façon dont vous spécifiez la table d’historique :

  • Tableau temporel avec une table d’historique anonyme : vous spécifiez le schéma de la table actuelle et laissez le système créer une table d’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 d’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 façon la plus simple de créer une table temporelle car elle ne nécessite aucun paramètre dans la SYSTEM_VERSIONING clause. L’exemple suivant crée une nouvelle table avec la version système activée, sans définir le nom de la table 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
);

Remarks

Une table temporelle à versionnement géré par le système doit avoir une clé primaire définie et exactement une définition PERIOD FOR SYSTEM_TIME avec deux colonnes datetime2, déclarées 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.

Le Moteur de base de données crée automatiquement une table d’historique anonyme dans le même schéma que la table actuelle ou temporelle.

Le nom de la table d’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 d’historique est créée en tant que table orientée lignes. 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 d’historique avec un nom autogénéré dans le format IX_<history_table_name>. L’index cluster contient les colonnes PERIOD (début, fin).

Dans la base de données Fabric SQL, la table d’historique créée n’est pas mise en miroir sur Fabric OneLake.

Pour créer la table actuelle en tant que table optimisée en mémoire, consultez Tables temporelles versionnées par le système avec des tables optimisées en mémoire.

Créer une table temporelle avec une table d’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. L’exemple suivant crée une nouvelle table avec le système de versionnement activé, avec le nom de la table 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
    )
);

Remarks

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

Créer une table temporelle avec une table d’historique définie par l’utilisateur est une option pratique lorsque vous souhaitez spécifier une table d’historique avec des options de stockage spécifiques et différents index adaptés aux requêtes historiques. Dans l’exemple suivant, vous créez une table d’historique définie par l’utilisateur avec un schéma aligné avec la table temporelle. Cette table d’historique possède un index columnstore clusterisé et un index rowstore non clusterisé supplémentaire (B-tree) pour les recherches ponctuelles. Après avoir créé la table d’historique, vous créez la table temporelle et spécifiez la table d’historique définie par l’utilisateur comme table d’historique par défaut.

Note

De manière générale, la documentation SQL Server 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 index sur les 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
    )
);

Remarks

Si vous prévoyez d’effectuer des requêtes analytiques sur les données historiques utilisant des agrégats ou des fonctions de fenêtrage, il est fortement recommandé de créer un stock-mémoire en colonnes en cluster comme index principal pour la compression et la performance 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.

Dans la base de données Sql Fabric et dans Azure SQL Database avec la mise en miroir Fabric configurée, lorsque vous utilisez une table existante comme table d’historique lors de la création d’une table temporelle, la table existante cesse d’être mise en miroir.

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

Vous pouvez activer le versionnement système sur une table non temporelle existante, par exemple lorsque vous souhaitez migrer une solution temporelle personnalisée vers un support intégré.

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 des opérations 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) et qui ne sont pas 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.

Remarks

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. Il ne peut pas être spécifié comme une date et une heure situées 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’addition PERIOD effectue une vérification de la cohérence des données sur la table actuelle 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 au moment de l’activation de SYSTEM_VERSIONING, une vérification de cohérence des données est effectuée sur la table actuelle et la table d’historique. Elle peut être ignorée si vous spécifiez DATA_CONSISTENCY_CHECK = OFF comme paramètre supplémentaire.

Migrer les 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. Cet exemple suppose que la solution personnalisée actuelle divise les données actuelles et historiques en deux tables utilisateur distinctes (ProjectTaskCurrent et ProjectTaskHistory).

Si votre solution existante utilise une seule table pour stocker les lignes réelles et historiques, vous devriez alors diviser les données en deux tables avant les étapes de migration illustrées dans l’exemple suivant. Commencez par supprimer le déclencheur de 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 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
        )
    );

Remarks

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’addition PERIOD effectue une vérification de la cohérence des données sur la table actuelle pour s’assurer que les valeurs existantes pour 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 cachées sont préférées, utilisez la commande suivante :

ALTER TABLE [tableName]
    ALTER COLUMN [columnName] ADD HIDDEN;

Contenu connexe

  • Last updated on