次の方法で共有


システム バージョン管理のテンポラル テーブルのデータを変更する

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance

システム バージョン管理のテンポラル テーブル内のデータは、通常のデータ操作言語 (DML) ステートメントを使用して変更しますが、1 つの重要な違いがあり、期間の列のデータは直接変更できません。 データが更新されると、バージョンが更新されて、更新された各行の以前のバージョンが、履歴テーブルに挿入されます。 データが削除された場合、削除は論理的で、行が現在のテーブルから履歴テーブルに移動され、データは完全に削除されるわけではありません。

データを挿入する

新しいデータを挿入する際に、PERIOD 列が HIDDEN でない場合、それらの列を考慮する必要があります。 テンポラル テーブルでは、パーティション切り替えを使用することもできます。

表示する period 列のある新しいデータの挿入

表示する INSERT 列がある場合、次のように PERIOD ステートメントを構築して、PERIOD 列を考慮できます。

INSERT ステートメントで列リストを指定する場合、システムによって、PERIOD 列の値が自動的に生成されるため、それらの列を省略できます。

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

INSERT ステートメントで列リストに PERIOD 列を指定した場合、それらの値として DEFAULT を指定する必要があります。

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

INSERT ステートメントで列リストを指定しない場合は、DEFAULT 列に PERIOD を指定します。

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

HIDDEN period 列のあるテーブルへのデータの挿入

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_VERSIONINGOFF に設定し、現在のテーブルと履歴テーブルの行を更新した場合、システムは変更履歴を保持しません。

現在のテーブルを更新する

この例では、ManagerIDDeptID である各行に対し、10 列が更新されます。 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 日現在の履歴テーブル内の値に戻すことを示しています。ここでは、DeptID10 です。

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_VERSIONINGON の間、履歴テーブルから行を直接削除することはできません。 SYSTEM_VERSIONING = OFF を設定し、現在のテーブルと履歴テーブルから行を削除した場合、システムは変更履歴を保持しません。

次のステートメントは SYSTEM_VERSIONING = ON の間サポートされていません。

  • TRUNCATE
  • 現在のテーブルの場合、SWITCH PARTITION OUT
  • 履歴テーブルの場合、SWITCH PARTITION IN

MERGE を使用してテンポラル テーブルのデータを変更する

INSERT 操作は、MERGE および 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);