修改系統建立版本時態表中的資料

  • 發行項

適用於：SQL Server 2016 (13.x) 和更新版本 Azure SQL 資料庫 Azure SQL 受控執行個體

系統版本設定時態表中的資料是使用一般的資料操作語言 (DML) 陳述式來修改，但有一個重要差異：期間資料行的資料是無法直接修改的。 更新資料時，即會為其建立版本，且會將每個更新資料列的舊版本插入至歷程記錄資料表。 刪除資料時，刪除是邏輯性的，且會將該資料列從歷程記錄資料表移至目前的資料表；而不會將其永久刪除。

插入資料

當您插入新資料時，必須說明 PERIOD 資料行 (如果其不是 HIDDEN)。 您也可以搭配時態表使用資料分割切換。

使用可見的期間資料行插入新資料

當您擁有如下的可見 PERIOD 資料行來說明新的 PERIOD 資料行時，可以建構 INSERT 陳述式︰

如果您在 INSERT 陳述式中指定資料行清單，則可省略 PERIOD 資料行，因為系統將自動產生這些資料行的值。

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

如果您在 PERIOD 陳述式的資料行清單中指定 INSERT 資料行，就必須將 DEFAULT 指定為其值。

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

如果您未在 INSERT 陳述式中指定資料行清單，請為 PERIOD 資料行指定 DEFAULT。

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

將資料插入含有 HIDDEN 期間資料行的資料表

如果將 PERIOD 資料行指定為 HIDDEN，則您不需要說明 INSERT 陳述式中的 PERIOD 資料行。 此行為可確保舊版應用程式在啟用將受益於版本設定的資料表上啟用系統版本設定時仍能繼續運作。

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');

使用 PARTITION SWITCH 插入資料

如果目前的資料表已進行資料分割，您可以使用 PARTITION SWITCH 作為有效率的機制，來將資料載入至空的分割區，或以平行方式載入至多個分割區。

在 PARTITION SWITCH IN 陳述式中與時態表搭配使用暫存表格必須已定義 SYSTEM_TIME PERIOD，但其不需要是時態表。 這可確保時態一致性檢查會在資料插入暫存表格期間執行，或是在將 SYSTEM_TIME 期間新增至預先填入暫存表格時執行。

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

如果您嘗試從不含期間定義的資料表執行 PARTITION SWITCH，將會得到錯誤訊息︰

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.

更新資料

您可以利用一般的 UPDATE 陳述式，來更新目前資料表中的資料。 您可以針對「災害」案例，從歷程記錄資料表中更新目前資料表中的資料。 但是，您不能更新 PERIOD 資料行，而且當 SYSTEM_VERSIONING = ON 時，您無法直接更新歷程記錄資料表中的資料。

如果您將 SYSTEM_VERSIONING 設為 OFF 並更新目前和歷程記錄資料表中的資料列，則系統不會保留變更的歷程記錄。

更新目前的資料表

在此範例中，針對 DeptID 為 10 的每個資料列更新 ManagerID 資料行。 您不能以任何方式參考 PERIOD 資料行。

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

但是，您不能更新 PERIOD 資料行，而且您無法更新歷程記錄資料表。 在此範例中，嘗試更新 PERIOD 資料行會產生錯誤。

UPDATE [dbo].[Department]
SET ValidFrom = '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'.

從歷程記錄資料表更新目前的資料表

您可以在目前資料表中使用 UPDATE，將實際資料列狀態還原為過去某個特定時間點的有效狀態。 將此視為還原為「上次已知良好的資料列版本」。 下列範例顯示還原至歷程記錄資料表中截至 2015 年 4 月 25 日且 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;

刪除資料

您可以利用一般的 DELETE 陳述式，來刪除目前資料表中的資料。 已刪除資料列的結束期間資料行將填入基礎交易的開始時間。 當 SYSTEM_VERSIONING 為 ON 時，您無法從歷程記錄資料表直接刪除資料列。 如果您從目前和歷程記錄資料表設定 SYSTEM_VERSIONING = OFF 和刪除資料列，則系統不會保留變更的歷程記錄。

在 SYSTEM_VERSIONING = ON 時不支援下列陳述式：

  • TRUNCATE
  • 目前資料表的 SWITCH PARTITION OUT
  • 歷程記錄資料表的 SWITCH PARTITION IN

使用 MERGE 修改時態表中的資料

MERGE 作業是透過與 INSERT 和 UPDATE 陳述式都有相關 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);

相關內容