Modification des données dans une table temporelle avec version gérée par le systèmeModifying data in 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

Les données d'une table temporelle avec système par version sont modifiées à l'aide d’instructions 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.Data in a system-versioned temporal table is modified using regular DML statements with one important difference: period column data cannot be directly modified. 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.When data is updated, it is versioned, with the previous version of each updated row is inserted into the history table. Lorsque des données sont supprimées, la suppression est logique : la ligne est déplacée dans la table d'historique à partir de la table actuelle et n'est pas définitivement supprimée.When data is deleted, the delete is logical, with the row moved into the history table from the current table - it is not permanently deleted.

Insertion de donnéesInserting data

Lorsque vous insérez de nouvelles données, vous devez prendre en compte les colonnes PERIOD si elles ne sont pas HIDDEN.When you insert new data, you need to account for the PERIOD columns if they are not HIDDEN. Vous pouvez également utiliser un basculement de partition avec des tables temporelles avec système par version.You can also use partition switching with system-versioned temporal tables.

Insérer de nouvelles données avec des colonnes de période visiblesInsert new data with visible period columns

Vous pouvez construire votre instruction INSERT si vous utilisez des colonnes PERIOD visibles afin de prendre en compte les nouvelles colonnes PERIOD :You can construct your INSERT statement when you have visible PERIOD columns as follows to account for the new PERIOD columns:

  • 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.If you specify the column list in your INSERT statement, you can omit the PERIOD columns because system will generate values for these columns automatically.

    -- 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 .If you do specify the PERIOD columns in the column list in your INSERT statement, then you need to specify DEFAULT as their value.

    INSERT INTO [dbo].[Department]
      (  [DeptID]
            , [DeptName]
            , [ManagerID]
            , [ParentDeptID]
            , SysStartTime
            , SysEndTime
      )
         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 .If you do not specify the column list in your INSERT statement, specify DEFAULT for PERIOD columns.

      -- Insert without 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)Insert data into a table with HIDDEN period columns

Si des colonnes PERIOD sont spécifiées comme étant HIDDEN (masquées), il vous suffit de spécifier les valeurs des colonnes visibles lorsque vous utiliser l’instruction INSERT sans spécifier la liste des colonnes.If PERIOD columns are specified as HIDDEN, then you need only to specify the values for the visible columns when you use INSERT without specifying the column list. Vous n'avez pas besoin de tenir compte des nouvelles colonnes PERIOD dans votre instruction INSERT .You do not need to account for the new PERIOD columns in your INSERT statement. 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.This behavior guarantees that your legacy applications will continue to work when you enable system-versioning on tables that will benefit from versioning.

CREATE TABLE [dbo].[CompanyLocation]
(
     [LocID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
   , [LocName] [varchar](50) NOT NULL
   , [City] [varchar](50) NOT NULL
   , [SysStartTime] [datetime2] GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
   , [SysEndTime] [datetime2] GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
   , PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
)
WITH ( SYSTEM_VERSIONING = ON );
GO
INSERT INTO [dbo].[CompanyLocation]
VALUES ('Headquarters', 'New York');

Insertion de données à l'aide du COMMUTATEUR DE PARTITIONInserting data using PARTITION SWITCH

Si la table actuelle est partitionnée, vous pouvez utiliser le basculement de partition comme un mécanisme efficace pour charger les données dans une partition vide ou dans plusieurs partitions en parallèle.If the current table is partitioned, you can use partition switch as an efficient mechanism to load data into an empty partition or to load into multiple partitions in parallel.

La table intermédiaire utilisée dans l’instruction PARTITION SWITCH IN avec une table temporelle avec système par version doit avoir une valeur SYSTEM_TIME PERIOD définie, mais elle ne doit pas être une table temporelle avec système par version.The staging table that is used in the PARTITION SWITCH IN statement with a system-versioned temporal table must have SYSTEM_TIME PERIOD defined, but it does not need to be a system-versioned temporal table. Cela garantit que des vérifications de cohérence temporelle sont effectuées lorsque des données sont insérées dans une table intermédiaire ou qu’une période SYSTEM_TIME est ajoutée à une table intermédiaire préremplie.This ensures that temporal consistency checks are performed during the data insert into a staging table or when SYSTEM_TIME period is added to a pre-populated staging table.

/*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
   , [SysStartTime] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL
   , [SysEndTime] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME ( [SysStartTime], [SysEndTime] )
) 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 BASCULEMENT DE PARTITION à 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.If you try to perform PARTITION SWITCH from a table without period definition you'll get error message: 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.

Mise à jour des donnéesUpdating data

Vous mettez à jour les données de la table actuelle avec une instruction UPDATE normale.You update data in the current table with a regular UPDATE statement. Vous pouvez mettre à jour les données de la table actuelle à partir de la table d'historique pour le scénario « Désolé ».You can update data in the current table from the history table for the "oops" scenario. 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.However, you cannot update PERIOD columns and you cannot directly update data in the history table while SYSTEM_VERSIONING = ON.

Définissez SYSTEM_VERSIONING = OFF et mettez à jour les lignes de la table actuelle et de la table d’historique, mais n’oubliez pas qu’avec cette procédure, le système ne conserve pas l’historique des modifications.Set SYSTEM_VERSIONING = OFF and update rows from current and history table but keep in mind that way system will not preserve history of changes.

Mise à jour de la table actuelleUpdating the current table

Dans cet exemple, la colonne ManagerID est mise à jour pour chaque ligne où DeptID = 10.In this example, the ManagerID column is updated for each row where the DeptID = 10. Les colonnes PERIOD ne sont référencées d’aucune façon.The PERIOD columns are not referenced in any way.

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.However, you cannot update a PERIOD column and you cannot update the history table. Dans cet exemple, une tentative de mise à jour d’une colonne PERIOD génère une erreur.In this example, an attempt to update a PERIOD column generates an error.

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

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

Mise à jour de la table actuelle à partir de la table d'historiqueUpdating the current table from the history table

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é (retour à la « dernière bonne version de ligne connue »).You can use UPDATE on the current table to revert the actual row state to valid state at a specific point in time in the past (reverting to a "last good known row version"). L'exemple suivant montre un retour des valeurs dans la table d'historique en date du 2015-04-25 où DeptID = 10.The following example shows reverting to the values in the history table as of 2015-04-25 where the DeptID = 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 ;

Effacement de donnéesDeleting data

Vous supprimez les données de la table actuelle avec une instruction DELETE normale.You delete data in the current table with a regular DELETE statement. La colonne de période de fin des lignes supprimées contiendra l'heure de début de la transaction sous-jacente.The end period column for deleted rows will be populated with the begin time of underlying transaction. Vous ne pouvez pas directement supprimer des lignes d’une table d’historique si SYSTEM_VERSIONING = ON.You cannot directly delete rows from history table while SYSTEM_VERSIONING = ON. Définissez SYSTEM_VERSIONING = OFF et supprimez les lignes de la table actuelle et de la table d’historique, mais n’oubliez pas qu’avec cette procédure, le système ne conserve pas l’historique des modifications.Set SYSTEM_VERSIONING = OFF and delete rows from current and history table but keep in mind that way system will not preserve history of changes. TRUNCATE, SWITCH PARTITION OUT de la table en cours et SWITCH PARTITION IN de la table d’historique ne sont pas pris en charge si SYSTEM_VERSIONING = ON.TRUNCATE, SWITCH PARTITION OUT of current table and SWITCH PARTITION IN history table are not supported while SYSTEM_VERSIONING = ON.

Utilisation de MERGE pour modifier les données d’une table temporelleUsing MERGE to modify data in temporal table

L’opérationMERGE est prise en charge avec les mêmes limitations que celles que les instructions INSERT et UPDATE ont concernant les colonnes PERIOD .MERGE operation is supported with the same limitations that INSERT and UPDATE statements have regarding PERIOD columns.

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 (DeptName)
   VALUES (source.DeptName);

Étapes suivantesNext steps