システム バージョン管理されたテンポラル テーブルを作成する
適用対象: 
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] です。 サフィックスは省略可能であり、テーブル名の最初の部分が一意ではない場合にのみ追加されます。
履歴テーブルは、行ストア テーブルとして作成されます。 可能な場合はページの圧縮が適用されます。不可能な場合は、履歴テーブルは圧縮されません。 たとえば、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+ ツリー) インデックスが、ポイント参照用に作成されます。 このユーザー定義の履歴テーブルを作成した後、ユーザー定義の履歴テーブルを既定の履歴テーブルとして指定して、システム バージョン管理されたテンポラル テーブルを作成します。
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;コマンドを使用します。
次のステップ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示