Partage via

Modifier des données dans une table temporelle avec version gérée par le système

  • Article

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

Les données d'une table temporelle avec système par version sont modifiées à l'aide d’instructions de langage de manipulation de données (DML) régulières avec une différence importante : les données de la colonne de période ne peuvent pas être directement modifiées. Lorsque des données sont mises à jour, des versions sont générées, et la version précédente de chaque ligne mise à jour est insérée dans la table d'historique. Lorsque des données sont supprimées, la suppression est logique, et la ligne est déplacée dans la table d'historique à partir de la table actuelle ; les données ne sont pas définitivement supprimées.

Insertion des données

Lorsque vous insérez de nouvelles données, vous devez prendre en compte les colonnes PERIOD si elles ne sont pas HIDDEN. Vous pouvez également utiliser un basculement de partition avec des tables temporelles.

Insérer de nouvelles données avec des colonnes de période visibles

Vous pouvez construire votre instruction INSERT si vous utilisez des colonnes PERIOD visibles afin de prendre en compte les nouvelles colonnes PERIOD :

Si vous spécifiez la liste des colonnes dans votre instruction INSERT, vous pouvez omettre les colonnes PERIOD car le système générera automatiquement des valeurs pour ces colonnes.

-- Insert with column list and without period columns
INSERT INTO [dbo].[Department] (
      [DeptID],
      [DeptName],
      [ManagerID],
      [ParentDeptID]
)
VALUES (10, 'Marketing', 101, 1);

Si vous ne spécifiez pas les colonnes PERIOD pour la liste des colonnes dans votre instruction INSERT, vous devez leur attribuer la valeur DEFAULT.

INSERT INTO [dbo].[Department] (
   DeptID,
   DeptName,
   ManagerID,
   ParentDeptID,
   ValidFrom,
   ValidTo
)
VALUES (11, 'Sales', 101, 1, DEFAULT, DEFAULT);

Si vous ne spécifiez pas la liste des colonnes dans votre instruction INSERT, spécifiez DEFAULT pour les colonnes PERIOD.

-- Insert without a column list and DEFAULT values for period columns
INSERT INTO [dbo].[Department]
VALUES(12, 'Production', 101, 1, DEFAULT, DEFAULT);

Insérer des données dans une table avec des colonnes de période HIDDEN (masquées)

Si les colonnes PERIOD sont spécifiées en tant que HIDDEN, vous n’avez pas besoin de tenir compte des colonnes PERIOD de votre instruction INSERT. Ce comportement garantit que vos applications héritées continuent de fonctionner lorsque vous activez le contrôle de version système sur des tables qui bénéficieront de ce contrôle.

CREATE TABLE [dbo].[CompanyLocation] (
    [LocID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [LocName] [varchar](50) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (SYSTEM_VERSIONING = ON);
GO

INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

Insertion de données à l'aide du BASCULEMENT DE PARTITION

Si la table actuelle est partitionnée, vous pouvez utiliser PARTITION SWITCH comme un mécanisme efficace pour charger les données dans une partition vide, ou dans plusieurs partitions en parallèle.

La table de mise en lots utilisée dans l’instruction PARTITION SWITCH IN avec une table temporelle doit avoir SYSTEM_TIME PERIOD de défini, mais elle n’a pas besoin d’être une table temporelle. Cela garantit que des vérifications de cohérence temporelle sont effectuées lorsque des données sont insérées dans une table de mise en lots ou qu’une période SYSTEM_TIME est ajoutée à une table de mise en lots préremplie.

/* Create staging table with period definition for SWITCH IN temporal table */
CREATE TABLE [dbo].[Staging_Department_Partition2] (
    [DeptID] [int] NOT NULL,
    [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])
) ON [PRIMARY]

/* Create aligned primary key */
ALTER TABLE [dbo].[Staging_Department_Partition2]
ADD CONSTRAINT [Staging_Department_Partition2_PK]
PRIMARY KEY CLUSTERED ([DeptID] ASC) ON [PRIMARY];

/*
Create and enforce constraints for partition boundaries.
Partition 2 contains rows with DeptID > 100 and DeptID <=200
*/
ALTER TABLE [dbo].[Staging_Department_Partition2]
WITH CHECK ADD CONSTRAINT [chk_staging_Department_partition_2] CHECK (
   [DeptID] > N'100'
   AND [DeptID] <= N'200'
);

ALTER TABLE [dbo].[Staging_Department_Partition2]
CHECK CONSTRAINT [chk_staging_Department_partition_2];

/*Load data into staging table*/
INSERT INTO [dbo].[staging_Department] (
    [DeptID],
    [DeptName],
    [ManagerID],
    [ParentDeptID]
    )
VALUES (101, 'D101', 1, NULL);

/*Use PARTITION SWITCH IN to efficiently add data to current table */
ALTER TABLE [Staging_Department]
SWITCH TO [dbo].[Department] PARTITION 2;

Si vous essayez d’effectuer un PARTITION SWITCH à partir d'une table sans définition de période, vous obtenez ce message d'erreur :

Msg 13577, Level 16, State 1, Line 25 ALTER TABLE SWITCH statement failed on table 'MyDB.dbo.Staging_Department_2015_09_26' because target table has SYSTEM_TIME PERIOD while source table does not have it.

Mettre à jour des données

Vous mettez à jour les données de la table actuelle avec une instruction UPDATE normale. Vous pouvez mettre à jour les données de la table actuelle à partir de la table d'historique pour le scénario catastrophe. Toutefois, vous ne pouvez pas mettre à jour les colonnes PERIOD et que vous ne pouvez pas directement mettre à jour les données de la table d’historique si SYSTEM_VERSIONING = ON.

Si vous définissez SYSTEM_VERSIONING sur OFF et mettez à jour les lignes des tables actuelles et historiques, le système ne conserve pas l’historique des modifications.

Mettre à jour la table actuelle

Dans cet exemple, la colonne ManagerID est mise à jour pour chaque ligne où DeptID est 10. Les colonnes PERIOD ne sont référencées d’aucune façon.

UPDATE [dbo].[Department]
SET [ManagerID] = 501
WHERE [DeptID] = 10;

Toutefois, vous ne pouvez pas mettre à jour une colonne PERIOD et vous ne pouvez pas mettre à jour la table d'historique. Dans cet exemple, une tentative de mise à jour d’une colonne PERIOD génère une erreur.

UPDATE [dbo].[Department]
SET ValidFrom = '2015-09-23 23:48:31.2990175'
WHERE DeptID = 10;

L’instruction suivante peut générer l’erreur suivante.

Msg 13537, Level 16, State 1, Line 3
Cannot update GENERATED ALWAYS columns in table 'TmpDev.dbo.Department'.

Mettre à jour la table actuelle à partir de la table d'historique

Vous pouvez utiliser UPDATE sur la table actuelle pour rétablir l’état réel de la ligne à un état valide à un moment précis dans le passé. Considérez cela comme revenir à une dernière version de ligne connue. L'exemple suivant montre un retour des valeurs dans la table d'historique en date du 25 avril 2015 où DeptID est 10.

UPDATE Department
SET DeptName = History.DeptName
FROM Department
FOR SYSTEM_TIME AS OF '2015-04-25' AS History
WHERE History.DeptID = 10
    AND Department.DeptID = 10;

Supprimer des données

Vous supprimez les données de la table actuelle avec une instruction DELETE normale. La colonne de période de fin des lignes supprimées contient l'heure de début de la transaction sous-jacente. Vous ne pouvez pas directement supprimer des lignes d’une table d’historique si SYSTEM_VERSIONING est ON. Si vous définissez SYSTEM_VERSIONING = OFF et supprimez des lignes des tables actuelles et historiques, le système ne conserve pas l’historique des modifications.

Les instructions suivantes ne sont pas prises en charge lorsque SYSTEM_VERSIONING = ON :

  • TRUNCATE
  • SWITCH PARTITION OUT pour la table actuelle
  • SWITCH PARTITION IN pour la table d’historique

Utilisez MERGE pour modifier les données d’une table temporelle

L’opération MERGE est prise en charge avec les mêmes limitations que celles que les instructions INSERT et UPDATE ont concernant les colonnes PERIOD.

CREATE TABLE DepartmentStaging (
    DeptId INT,
    DeptName VARCHAR(50)
);
GO

INSERT INTO DepartmentStaging
VALUES (1, 'Company Management');

INSERT INTO DepartmentStaging
VALUES (10, 'Science & Research');

INSERT INTO DepartmentStaging
VALUES (15, 'Process Management');

MERGE dbo.Department AS target
USING (
    SELECT DeptId, DeptName
    FROM DepartmentStaging
    ) AS source(DeptId, DeptName)
    ON (target.DeptId = source.DeptId)
WHEN MATCHED
    THEN UPDATE SET DeptName = source.DeptName
WHEN NOT MATCHED
    THEN
        INSERT (DeptId, DeptName)
        VALUES (source.DeptId, source.DeptName);

Contenu connexe