Création d’une table temporelle avec versions gérées par le systèmeCreating a system-versioned temporal table

S’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) et ultérieures OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed InstanceS’applique à :Applies to: ouiSQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later OuiAzure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database OuiAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance

Il existe trois façons de créer une table temporelle avec gestion de version du système en ce qui concerne la manière dont la table de l’historique est spécifiée :There are three ways to create a system-versioned temporal table with regards to how the history table is specified:

  • 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.Temporal table with an anonymous history table: you specify the schema of the current table and let the system create a corresponding history table with auto-generated name.
  • 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.Temporal table with a default history table: you specify the history table schema name and table name and let the system create a history table in that schema.
  • 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.Temporal table with a user-defined history table created beforehand: you create a history table that fits best your needs and then reference that table during temporal table creation.

Création d’une table temporelle avec une table de l’historique anonymeCreating a temporal table with an anonymous history table

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.Creating a temporal table with an "anonymous" history table is a convenient option for quick object creation, especially in prototypes and test environments. Il s’agit aussi de 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.It is also the simplest way to create a temporal table since it doesn't require any parameter in SYSTEM_VERSIONING clause. Dans l’exemple ci-dessous, 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.In the example below, a new table is created with system-versioning enabled without defining the name of the history table.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Remarques importantesImportant remarks

  • Une table temporelle avec gestion de version du système doit avoir une clé primaire définie et précisément une instruction PERIOD FOR SYSTEM_TIME définie avec deux colonnes datetime2, déclarée comme GENERATED ALWAYS AS ROW START / END.A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END
  • Les colonnes PERIOD sont toujours considérées comme n’acceptant pas les valeurs Null, même si la possibilité de valeur Null n’est pas spécifiée.The PERIOD columns are always assumed to be non-nullable, even if nullability is not specified. Si les colonnes PERIOD sont explicitement définies comme acceptant les valeurs Null, l’instruction CREATE TABLE échoue.If thePERIOD columns are explicitly defined as nullable, the CREATE TABLE statement will fail.
  • La table de l’historique doit toujours être alignée par schéma sur la table actuelle ou temporelle, en termes de nombre de colonnes, de noms de colonnes, de classement et de types de données.The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.
  • Une table de l’historique anonyme est créée automatiquement sur le même schéma que la table en cours ou temporelle.An anonymous history table is automatically created in the same schema as current or temporal table.
  • Le nom de la table de l’historique anonyme a le format suivant : MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix] .The anonymous history table name has the following format: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_[suffix]. Le suffixe est facultatif. Il est ajouté uniquement si la première partie du nom de la table n’est pas unique.Suffix is optional and it will be added only if the first part of the table name is not unique.
  • La table de l’historique est créée en tant que table rowstore.The history table is created as a rowstore table. Un compression de page est appliquée si possible. Autrement, la table de l’historique est décompressée.PAGE compression is applied if possible, otherwise the history table will be uncompressed. Par exemple, certaines configurations de table, telles des colonnes fragmentées, n’autorisent pas la compression.For example, some table configurations, such as SPARSE columns, do not allow 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> .A default clustered index is created for the history table with an auto-generated name in format IX_<history_table_name>. L’index cluster contient les colonnes PERIOD (début, fin).The clustered index contains the PERIOD columns (end, start).
  • 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.To create the current table as a memory-optimized table, see System-Versioned Temporal Tables with Memory-Optimized Tables.

Création d’une table temporelle avec une table de l’historique par défautCreating a temporal table with a default history table

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.Creating a temporal table with a default history table is a convenient option when you want to control naming and still rely on the system to create the history table with the default configuration. Dans l’exemple ci-dessous, 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.In the example below, a new table is created with system-versioning enabled with the name of the history table explicitly defined.

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Remarques importantesImportant 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.The history table is created using the same rules as apply to creating an "anonymous" history table, with the following rules that apply specifically to the named history table.

  • Le nom du schéma est obligatoire pour le paramètre HISTORY_TABLE .The schema name is mandatory for the HISTORY_TABLE parameter.
  • Si le schéma spécifié n’existe pas, l’instruction CREATE TABLE échoue.If the specified schema does not exist, the CREATE TABLE statement will fail.
  • 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.If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. Si vous spécifiez une table de l’historique non valide, l’instruction CREATE TABLE échoue.If you specify an invalid history table, the CREATE TABLE statement will fail.

Création d’une table temporelle avec une table de l’historique définie par l’utilisateurCreating a temporal table with a user-defined history table

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 des index supplémentaires spécifiques.Creating a temporal table with user-defined history table is a convenient option when the user wants to specify a history table with specific storage options and additional indexes. Dans l’exemple ci-dessous, 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 sera créée.In the example below, a user-defined history table is created with a schema that is aligned with the temporal table that will be created. Sur cette table de l’historique définie par l’utilisateur, un index columnstore cluster et un index rowstore (Btree) non cluster supplémentaire sont créés pour les recherches de point.To this user-defined history table, a clustered columnstore index and additional non clustered rowstore (B-tree) index is created for point lookups. 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.After this user-defined history table is created, the system-versioned temporal table is created specifying the user-defined history table as the default history table.

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

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory
    ON DepartmentHistory;
CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
    ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED
  , DeptName VARCHAR(50) NOT NULL
  , ManagerID INT NULL
  , ParentDeptID INT NULL
  , SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
  , SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
  , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

Remarques importantesImportant remarks

  • 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.If you plan to run analytic queries on the historical data that employs aggregates or windowing functions, creating a clustered columnstore as a primary index is highly recommended for compression and query performance.
  • Si l’usage principal est l’audit de données (par exemple, la recherche des modifications historiques d’une seule ligne de la table actuelle), un bon choix consiste à créer une table de l’historique rowstore avec un index cluster.If the primary use case is data audit (i.e. searching for historical changes for a single row from the current table), then a good choice is to create rowstore history table with a clustered index
  • La table de l’historique ne peut pas avoir de clé primaire, de clés étrangères, d’index uniques, de contraintes de table ou de déclencheurs.The history table cannot have a primary key, foreign keys, unique indexes, table constraints or triggers. 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.It cannot be configured for change data capture, change tracking, transactional or merge replication.

Modifier une table non temporelle pour la convertir en table temporelle avec contrôle de version du systèmeAlter non-temporal table to be a system-versioned temporal table

Cette solution est appropriée lorsque vous devez activer le contrôle de version du système à l’aide d’une table existante, par exemple, quand vous voulez migrer une solution temporelle personnalisée vers une prise en charge intégrée.When you need to enable system-versioning using an existing table, such as when you wish to migrate a custom temporal solution to built-in support. Par exemple, vous avez peut-être un ensemble de tables où le contrôle de version est implémenté avec des déclencheurs.For example, you may have a set of tables where versioning is implemented with triggers. L’utilisation d’un contrôle de version du système temporel est moins complexe et offre des avantages supplémentaires, notamment :Using temporal system-versioning is less complex and provides additional benefits including:

  • Historique immuableImmutable history
  • Nouvelle syntaxe pour les requêtes se déplaçant dans le tempsNew syntax for time-travelling queries
  • Meilleures performances DMLBetter DML performance
  • Coûts de maintenance minimalMinimal maintenance costs

Lors de la conversion d’une table existante, envisagez d’utiliser la clause HIDDEN pour masquer les nouvelles colonnes PERIOD (les colonnes dateheure2 SysStartTime et SysEndTime) afin d’éviter tout impact sur des applications existantes qui ne spécifient pas explicitement les noms de colonne (par ex. SELECT * ou INSERT sans liste de colonne) non conçues pour gérer de nouvelles colonnes.When converting an existing table, consider using the HIDDEN clause to hide the new PERIOD columns (the datetime2 columns SysStartTime and SysEndTime) to avoid impacting existing applications that do not explicitly specify column names (e.g. SELECT * or INSERT without column list) are not designed to handle new columns.

Ajout du contrôle de version à des tables non temporellesAdding versioning to non-temporal tables

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éera pour vous :If you want to start tracking changes for a non-temporal table that contains the data, you need to add the PERIOD definition and optionally provide a name for the empty history table that SQL Server will create for you:

CREATE SCHEMA History;
GO

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

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

Important

La précision de DATETIME2 doit être alignée sur la précision de la table sous-jacente. Consultez les remarques suivantes.The precision for DATETIME2 must align with the precision for the underlying table - see following remarks.

Remarques importantesImportant 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).Adding non-nullable columns with defaults to an existing table with data is a size of data operation on all editions other than SQL Server Enterprise Edition (on which it is a metadata operation). Sur SQL Server Édition 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.With a large existing history table with data on SQL Server Standard Edition, adding a non-null column can be an expensive operation.
  • Les contraintes applicables aux colonnes de fin et de début de la période doivent être choisies avec soin :Constraints for period start and period end columns must be carefully chosen:
    • 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.Default for start column specifies from which point in time you consider existing rows to be valid. Il ne peut pas s’agir d’un point DateHeure dans le futur.It cannot be specified as a datetime point in the future.
    • L’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.End time must be specified as the maximum value for a given datetime2 precision, for example 9999-12-31 23:59:59 or 9999-12-31 23:59:59.9999999.
  • L’ajout d’une période entraîne une vérification de cohérence des données sur la table actuelle pour s’assurer que les valeurs par défaut pour les colonnes de période sont valides.Adding period will perform a data consistency check on the current table to make sure that the defaults for period columns are valid.
  • 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.When an existing history table is specified when enabling SYSTEM_VERSIONING, a data consistency check will be performed across both the current and the history table. Elle peut être ignorée si vous spécifiez DATA_CONSISTENCY_CHECK = OFF comme paramètre supplémentaire.It can be skipped if you specify DATA_CONSISTENCY_CHECK = OFF as an additional parameter.

Migrer de tables existantes vers la prise en charge intégréeMigrate existing tables to built-in support

Cet exemple montre comment migrer une solution basée sur des déclencheurs vers la prise en charge temporelle intégrée.This example shows how to migrate an existing solution based on triggers to built-in temporal support. 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).For this example, we assume that the current custom solution splits the current and historical data in two separate user tables (ProjectTaskCurrent and 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 cet exemple :If your existing solution uses single table to store actual and historical rows, then you should split the data into two tables prior to the migration steps shown in this example:

/*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));

Remarques importantesImportant 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.Referencing existing columns in PERIOD definition implicitly changes generated_always_type to AS_ROW_START and AS_ROW_END for those columns.
  • 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.Adding PERIOD will perform a data consistency check on current table to make sure that the existing values for period columns are valid
  • Il est vivement recommandé 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.It is highly recommended to set SYSTEM_VERSIONING with DATA_CONSISTENCY_CHECK = ON to enforce data consistency checks on existing data.
  • Si les colonnes masquées sont préférables, utilisez la commande ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.If hidden columns are preferred, use the command ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;.

Étapes suivantesNext steps