システムバージョン管理されたテンポラルテーブルを作成する

[アーティクル]
08/02/2024

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

履歴テーブルの指定方法を検討するときに、システムバージョンのテンポラルテーブルを作成するには、次の 3 つの方法があります。

匿名履歴テーブルでのテンポラルテーブル: 現在のテーブルのスキーマを指定し、システムが自動生成された名前で対応する履歴テーブルを作成できるようにします。
既定の履歴テーブルでのテンポラルテーブル: 履歴テーブルのスキーマ名とテーブル名を指定し、システムがそのスキーマで履歴テーブルを作成できるようにします。
あらかじめ作成してあるユーザー定義履歴テーブルでのテンポラルテーブル: ニーズに最適な履歴テーブルをユーザーが作成し、テンポラルテーブルの作成時にそのテーブルを参照します。

匿名履歴テーブルによりテンポラルテーブルを作成する

匿名履歴テーブルを使用したテンポラルテーブルの作成は、すばやくオブジェクトを作成するための便利なオプションであり、プロトタイプおよびテスト環境で特に有効です。また、SYSTEM_VERSIONING 句でパラメーターを指定する必要がないため、テンポラルテーブルを作成する最も簡単な方法でもあります。次の例では、履歴テーブルの名前を定義せずに、システムバージョン管理を有効にして新しいテーブルが作成されます。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON);

解説

システムバージョン管理されたテンポラルテーブルには、主キーが定義されており、GENERATED ALWAYS AS ROW START または GENERATED ALWAYS AS ROW END として宣言された 2 つの datetime2 列で定義された 1 つの PERIOD FOR SYSTEM_TIME が必要です。

PERIOD 列は、null 値許容性が指定されていない場合でも、常に null 値非許容と見なされます。 PERIOD 列が明示的に Null 許容として定義されている場合、CREATE TABLE ステートメントは失敗します。

履歴テーブルは、列の数、列名、順序、データ型に関して、常に現在のテーブルまたはテンポラルテーブルとスキーマが整合している必要があります。

匿名履歴テーブルは、現在のテーブルまたはテンポラルテーブルと同じスキーマで自動的に作成されます。

匿名履歴テーブル名は次の形式になります: MSSQL_TemporalHistoryFor_<current_temporal_table_object_id>_<suffix>。サフィックスは省略可能であり、テーブル名の最初の部分が一意ではない場合にのみ追加されます。

履歴テーブルは、行ストアテーブルとして作成されます。可能な場合は PAGE の圧縮が適用されます。不可能な場合は、履歴テーブルは圧縮されません。たとえば、SPARSE 列などの一部のテーブル構成では、圧縮は許可されません。

履歴テーブルの既定のクラスター化インデックスは、IX_<history_table_name> という形式で自動生成された名前で作成されます。クラスター化インデックスには、 PERIOD 列 (終了、開始) が含まれます。

メモリ最適化テーブルとして現在のテーブルを作成する場合は、「メモリ最適化テーブルでのシステムバージョン管理されたテンポラルテーブル」を参照してください。

既定の履歴テーブルによるテンポラルテーブルの作成

既定の履歴テーブルによるテンポラルテーブルの作成は、名前付けを制御しながら、一方で既定の構成による履歴テーブルの作成はシステムに任せたい場合に、便利なオプションです。次の例では、システムバージョン管理が有効になっており、履歴テーブルの名前が明示的に定義された新しいテーブルが作成されます。

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

解説

履歴テーブルは "匿名" 履歴テーブルの作成に適用されるのと同じ規則を使用して作成されますが、以下の規則は名前付き履歴テーブルだけに適用されます。

HISTORY_TABLE パラメーターにはスキーマ名が必須です。
指定したスキーマが存在しない場合、CREATE TABLE ステートメントは失敗します。
HISTORY_TABLE パラメーターによって指定されているテーブルが既に存在する場合、新しく作成されるテンポラルテーブルに対して、スキーマの整合性およびテンポラルデータの整合性が検証されます。無効な履歴テーブルが指定された場合、 CREATE TABLE ステートメントは失敗します。

ユーザー定義の履歴テーブルによるテンポラルテーブルの作成

ユーザー定義の履歴テーブルによるテンポラルテーブルの作成は、ユーザーが特定のストレージオプションと履歴クエリに合わせて調整されたさまざまなインデックスで履歴テーブルを指定したい場合に便利なオプションです。次の例では、作成されるテンポラルテーブルと整合するスキーマで、ユーザー定義の履歴テーブルを作成します。このユーザー定義の履歴テーブルに対して、クラスター化列ストアインデックスおよび追加の非クラスター化行ストア (B ツリー) インデックスが、ポイント参照用に作成されます。このユーザー定義の履歴テーブルを作成した後、ユーザー定義の履歴テーブルを既定の履歴テーブルとして指定して、テンポラルテーブルを作成します。

Note

ドキュメントでは、一般にインデックスに関して B ツリーという用語が使用されます。行ストアインデックスで、データベースエンジンによって B+ ツリーが実装されます。これは、列ストアインデックスやメモリ最適化テーブルのインデックスには適用されません。詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザインガイド」を参照してください。

CREATE TABLE DepartmentHistory
(
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_DepartmentHistory ON DepartmentHistory;

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_Period_Columns
ON DepartmentHistory (ValidTo, ValidFrom, DeptID);
GO

CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED,
    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)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));

解説

集計関数またはウィンドウ関数を使用する分析クエリを履歴データに対して実行する予定の場合は、圧縮とクエリパフォーマンスを考慮し、プライマリインデックスとしてクラスター化列ストアを作成することを強くお勧めします。

テンポラルテーブルをデータの監査に使用する場合は (つまり、現在のテーブルから単一行の変更履歴を検索する場合)、クラスター化インデックスを含む行ストア履歴テーブルを作成する必要があります。

履歴テーブルに、主キー、外部キー、一意なインデックス、テーブル制約、トリガーを含めることはできません。変更データキャプチャ、変更追跡、トランザクションレプリケーション、またはマージレプリケーション用に構成することはできません。

非テンポラルテーブルをシステムバージョン管理されたテンポラルテーブルに変更する

カスタムテンポラルソリューションを組み込みサポートに移行する場合など、既存の非テンポラルテーブルでシステムバージョン管理を有効にすることができます。

たとえば、一連のテーブルにトリガーを使用してバージョン管理を実装できます。テンポラルシステムバージョン管理を使用すると、それほど複雑ではなく、他にも次のような利点もあります。

変更不可能な履歴
タイムトラベルクエリ用の新しい構文
DML パフォーマンスの向上
最小限のメンテナンスコスト

既存のテーブルを変換するときは、明示的に指定していない既存のアプリケーションに影響しないように、HIDDEN 句を使用して新しい PERIOD 列 (datetime2 列 ValidFrom および ValidTo) を非表示にすることを検討してください。列名 (たとえば、列リストのない SELECT * または INSERT) は、新しい列を処理するように設計されていません。

非テンポラルテーブルへのバージョン管理の追加

データを含む非テンポラルテーブルの変更の追跡を開始する場合は、PERIOD 定義を追加する必要があり、必要に応じて、SQL Server で作成される空の履歴テーブルの名前を指定します。

CREATE SCHEMA History;
GO

ALTER TABLE InsurancePolicy ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME(),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_InsurancePolicy_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);
GO

ALTER TABLE InsurancePolicy
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.InsurancePolicy));
GO

重要

DATETIME2 の精度は、基になるテーブルの精度に合わせる必要があります。

解説

データが含まれる既存のテーブルに既定値のある null 非許容列を追加することは、SQL Server Enterprise Edition 以外のすべてのエディションではデータ操作 (Enterprise Edition ではメタデータ操作) となります。 SQL Server Standard Edition では、データが含まれる既存の大規模な履歴テーブルの場合、非 null 列の追加はコストのかかる操作になることがあります。

期間開始列および期間終了列に対する制約は、慎重に選択する必要があります。

開始列の既定値では、既存の行が有効であると考慮することを始める時点を指定します。未来の時刻は指定できません。
終了日時は、特定の datetime2 精度に対する最大値として指定する必要があります。たとえば、9999-12-31 23:59:59 や 9999-12-31 23:59:59.9999999 にします。

PERIOD を追加すると、現在のテーブルでデータ整合性チェックが実行されて、期間列の既存の値が有効であることが確認されます。

SYSTEM_VERSIONING を有効にするときに既存の履歴テーブルが指定されている場合、現在のテーブルと履歴テーブルの両方に対してデータの整合性チェックが行われます。 DATA_CONSISTENCY_CHECK = OFF を追加パラメーターとして指定した場合は、スキップできます。

既存のテーブルを組み込みサポートに移行する

この例では、トリガーに基づく既存のソリューションから組み込みのテンポラルサポートに移行する方法を示します。この例では、現在のカスタムソリューションによって現在および過去のデータが 2 つの異なるユーザーテーブル (ProjectTaskCurrent と ProjectTaskHistory) に分割されるものとします。

既存のソリューションが 1 つのテーブルを使用して実際の行と過去の行を格納している場合は、次の例に示す移行手順を実行する前に、データを 2 つのテーブルに分割する必要があります。最初に、将来のテンポラルテーブルでトリガーを削除します。次に、PERIOD 列を null 非許容にします。

/* Drop trigger on future temporal table */
DROP TRIGGER ProjectCurrent_OnUpdateDelete;

/* Make sure that future period columns are non-nullable */
ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidFrom] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskHistory
ALTER COLUMN [ValidTo] DATETIME2 NOT NULL;

ALTER TABLE ProjectTaskCurrent ADD PERIOD
FOR SYSTEM_TIME([ValidFrom], [ValidTo]);

ALTER TABLE ProjectTaskCurrent SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.ProjectTaskHistory,
        DATA_CONSISTENCY_CHECK = ON
    )
);

解説

PERIOD 定義内の既存の列を参照すると、それらの列の generated_always_type が AS_ROW_START および AS_ROW_END に暗黙的に変更されます。

PERIOD を追加すると、現在のテーブルでデータ整合性チェックが実行されて、期間列の既存の値が有効であることが確認されます。

SYSTEM_VERSIONING と DATA_CONSISTENCY_CHECK = ON を設定し、既存データに対するデータ整合性チェックを強制的に行うことを強くお勧めします。

非表示列が優先される場合は、ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN; コマンドを使用します。

次の方法で共有

システムバージョン管理されたテンポラルテーブルを作成する

匿名履歴テーブルによりテンポラルテーブルを作成する

解説

既定の履歴テーブルによるテンポラルテーブルの作成

解説

ユーザー定義の履歴テーブルによるテンポラルテーブルの作成

解説

非テンポラルテーブルをシステムバージョン管理されたテンポラルテーブルに変更する

非テンポラルテーブルへのバージョン管理の追加

解説

既存のテーブルを組み込みサポートに移行する

解説

フィードバック

その他のリソース

次の方法で共有

システム バージョン管理されたテンポラル テーブルを作成する

匿名履歴テーブルによりテンポラル テーブルを作成する

解説

既定の履歴テーブルによるテンポラル テーブルの作成

解説

ユーザー定義の履歴テーブルによるテンポラル テーブルの作成

解説

非テンポラル テーブルをシステム バージョン管理されたテンポラル テーブルに変更する

非テンポラル テーブルへのバージョン管理の追加

解説

既存のテーブルを組み込みサポートに移行する

解説

関連するコンテンツ

フィードバック

その他のリソース

システムバージョン管理されたテンポラルテーブルを作成する

匿名履歴テーブルによりテンポラルテーブルを作成する

既定の履歴テーブルによるテンポラルテーブルの作成

ユーザー定義の履歴テーブルによるテンポラルテーブルの作成

非テンポラルテーブルをシステムバージョン管理されたテンポラルテーブルに変更する

非テンポラルテーブルへのバージョン管理の追加