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 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 commeGENERATED ALWAYS AS ROW START
ouGENERATED 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 lesPERIOD
colonnes sont explicitement définies comme pouvant être null, l’instructionCREATE 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’instructionCREATE 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 ValidFrom
datetime2 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
ou9999-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_VERSIONING
de , 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écifiezDATA_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 changegenerated_always_type
implicitement versAS_ROW_START
etAS_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
avecDATA_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
- Tables temporelles
- Prise en main avec des tables temporelles avec version système
- Gérer la conservation des données historiques dans des tables temporelles avec version système
- Tables temporelles avec version gérée par le système avec tables à mémoire optimisée
- CREATE TABLE (Transact-SQL)
- Modification de données dans une table temporelle avec version système
- Interrogation de données dans une table temporelle avec version système
- Modification du schéma d’une table temporelle avec version système
- Arrêt du contrôle de version système sur une table temporelle avec version système
Commentaires
https://aka.ms/ContentUserFeedback.
Prochainement : Tout au long de l'année 2024, nous supprimerons progressivement les GitHub Issues en tant que mécanisme de retour d'information pour le contenu et nous les remplacerons par un nouveau système de retour d'information. Pour plus d’informations, voir:Soumettre et afficher des commentaires pour