システム バージョン管理されたテンポラル テーブルを作成する
適用対象:
SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure 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);
解説
システム バージョン管理されたテンポラル テーブルには、主キーが定義されており、2 つの datetime2 列で定義された 1 つだけ
PERIOD FOR SYSTEM_TIMEが またはGENERATED ALWAYS AS ROW ENDとしてGENERATED ALWAYS AS ROW START宣言されている必要があります。null 許容が指定されていない場合でも、列は
PERIOD常に null 非許容であると見なされます。 列がPERIODnull 許容として明示的に定義されている場合、ステートメントは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+ ツリー) インデックスが、ポイント参照用に作成されます。 このユーザー定義の履歴テーブルを作成した後、ユーザー定義の履歴テーブルを既定の履歴テーブルとして指定して、システム バージョン管理されたテンポラル テーブルを作成します。
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エディション以外のすべてのエディションのデータ操作のサイズです。 SQL Server Standardエディションのデータを含む大きな既存の履歴テーブルでは、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参照すると、それらの列の とAS_ROW_ENDが暗黙的にAS_ROW_START変更generated_always_typeされます。 - を追加
PERIODすると、現在のテーブルでデータ整合性チェックが実行され、期間列の既存の値が有効であることを確認します - 既存のデータにデータ整合性チェックを適用するには、 を に設定
SYSTEM_VERSIONINGDATA_CONSISTENCY_CHECK = ONすることを強くお勧めします。 - 非表示列が優先される場合は、
ALTER TABLE [tableName] ALTER COLUMN [columnName] ADD HIDDEN;コマンドを使用します。
次のステップ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示