Изменение данных в темпоральной таблице с системным управлением версиямиModifying data in a system-versioned temporal table

Применимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) и более поздние версии ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed InstanceПрименимо к:Applies to: даSQL Server 2016 (13.x);SQL Server 2016 (13.x)yesSQL Server 2016 (13.x);SQL Server 2016 (13.x) and later ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database ДаУправляемый экземпляр SQL AzureAzure SQL Managed InstanceYesУправляемый экземпляр SQL AzureAzure SQL Managed Instance

Данные в темпоральной таблице с системным управлением версиями изменяются с помощью инструкций DML. Но нужно помнить об одном важном отличии: данные в столбце периода нельзя изменять напрямую.Data in a system-versioned temporal table is modified using regular DML statements with one important difference: period column data cannot be directly modified. Когда данные изменяются, они версионируются (предыдущая версия каждой измененной строки добавляется в таблицу журнала).When data is updated, it is versioned, with the previous version of each updated row is inserted into the history table. При удалении данных используется логическое удаление — из текущей таблицы строка перемещается в таблицу журнала (строка не удаляется безвозвратно).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.

Добавление данныхInserting data

При добавлении новых данных необходимо учитывать столбцы периодов ( PERIOD ), если они не скрыты ( HIDDEN).When you insert new data, you need to account for the PERIOD columns if they are not HIDDEN. С темпоральными таблицами с системным управлением версиями также можно использовать переключение секций.You can also use partition switching with system-versioned temporal tables.

Добавление новых данных с видимыми столбцами периодовInsert new data with visible period columns

Чтобы принять в расчет новые столбцы PERIOD , создать инструкцию INSERT при наличии видимых столбцов PERIOD можно так:You can construct your INSERT statement when you have visible PERIOD columns as follows to account for the new PERIOD columns:

  • При указании списка столбцов в инструкции INSERT столбцы PERIOD можно опустить, так как система создает для них значения автоматически.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
           ) ;
    
  • Если в инструкцииINSERT вы все же указываете столбцы PERIOD в списке столбцов, в качестве их значения необходимо указать 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) ;
    
  • Если вы не указываете в инструкции INSERT список столбцов, укажите для столбцов PERIOD значение DEFAULT .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);
    
    

Добавление данных в таблицу со скрытыми столбцами периодовInsert data into a table with HIDDEN period columns

Если столбцы PERIOD скрыты, вам нужно указать значения только для видимых столбцов (при условии, что вы добавляете данные, не указывая список столбцов).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. В инструкции INSERT не нужно учитывать новые столбцы PERIOD .You do not need to account for the new PERIOD columns in your INSERT statement. Это гарантирует, что устаревшие приложения будут и дальше работать после включения системного управления версиями в таблицах, для которых версионирование может пойти на пользу.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');

Добавление данных с использованием переключения секцийInserting data using PARTITION SWITCH

Если текущая таблица секционирована, переключение секций можно использовать как эффективный механизм загрузки данных в одну или несколько секций одновременно.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.

Для промежуточной таблицы, которая указана в инструкции PARTITION SWITCH IN с темпоральной таблицей с системным управлением версиями, необходимо определить период SYSTEM_TIME PERIOD , но промежуточная таблица не обязательно должна быть темпоральной с системным управлением версиями.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. Это гарантирует выполнение проверок темпоральной согласованности: 1) во время добавления данных в промежуточную таблицу; 2) во время добавления периода SYSTEM_TIME в предварительно заполненную промежуточную таблицу.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;

Если вы попытаетесь переключить секции из таблицы, в которой период не определен, появится сообщение об ошибке: 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.

Обновление данныхUpdating data

Данные в текущей таблице изменяются с помощью регулярной инструкции UPDATE .You update data in the current table with a regular UPDATE statement. Обновлять данные в текущей таблице, используя данные из таблицы журнала, можно в случаях ошибок или сбоев.You can update data in the current table from the history table for the "oops" scenario. Однако обновлять столбцы PERIOD нельзя. Также нельзя напрямую обновлять данные в таблице журнала, когда SYSTEM_VERSIONING = ON.However, you cannot update PERIOD columns and you cannot directly update data in the history table while SYSTEM_VERSIONING = ON.

Чтобы обновить строки из текущей таблицы и таблицы журнала, задайте SYSTEM_VERSIONING = OFF . Но в этом случае система не будет вести журнал изменений.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.

Обновление текущей таблицыUpdating the current table

В этом примере столбец ManagerID обновляется в каждой строке, где DeptID = 10.In this example, the ManagerID column is updated for each row where the DeptID = 10. Столбцы PERIOD здесь не указываются.The PERIOD columns are not referenced in any way.

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

Тем не менее обновить столбец PERIOD и таблицу журнала нельзя.However, you cannot update a PERIOD column and you cannot update the history table. В этом примере попытка обновить столбец PERIOD приведет к ошибке.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'.

Обновление текущей таблицы из таблицы журналаUpdating the current table from the history table

С помощью инструкции UPDATE строку из текущей таблицы можно обновить до состояния, которое было на определенный момент времени в прошлом (восстановление "последней хорошей версии строки").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"). В следующем примере показано восстановление значений строк, в которых DeptID = 10. Данные восстанавливаются из таблицы журнала по состоянию на 25.04.2015.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 ;

Удаление данныхDeleting data

Данные в текущей таблице удаляются с помощью регулярной инструкции DELETE .You delete data in the current table with a regular DELETE statement. В удаленных строках в столбец периода окончания будет подставлено время начала базовой транзакции.The end period column for deleted rows will be populated with the begin time of underlying transaction. Когда SYSTEM_VERSIONING = ON, удалять строки напрямую из таблицы журнала нельзя.You cannot directly delete rows from history table while SYSTEM_VERSIONING = ON. Чтобы удалить строки из текущей таблицы и таблицы журнала, задайте SYSTEM_VERSIONING = OFF . Но в этом случае система не будет вести журнал изменений.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. КогдаSWITCH PARTITION IN, TRUNCATE , SWITCH PARTITION OUT (для текущей таблицы) и SWITCH PARTITION IN(для таблицы журнала) не работают.TRUNCATE, SWITCH PARTITION OUT of current table and SWITCH PARTITION IN history table are not supported while SYSTEM_VERSIONING = ON.

Изменение данных в темпоральной таблице с помощью слиянияUsing MERGE to modify data in temporal table

ОперацияMERGE имеет такие же ограничения, что и инструкции INSERT и UPDATE относительно столбцов 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);

Дальнейшие действияNext steps