テンポラル テーブル

  • [アーティクル]

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

テンポラル テーブル (システム バージョン管理されたテンポラル テーブルともいう) は、現時点の正しいデータのみではなく、任意の時点でテーブルに格納されているデータに関する情報を提供するためのサポートが組み込まれているデータベース機能です。

システム バージョン管理されたテンポラル テーブルの使用を開始し、テンポラル テーブルの使用シナリオを確認してください。

システム バージョン管理されたテンポラル テーブルとは

システム バージョン管理されたテンポラル テーブルは、データ変更の履歴を完全に保持し、特定の時点の分析を簡単に実行できるように設計されたユーザー テーブルの一種です。 システムが各行 (つまりデータベース エンジン) の有効期間を管理しているため、この種類のテンポラル テーブルは、システム バージョン管理されたテンポラル テーブルと呼ばれます。

すべてのテンポラル テーブルには、それぞれに datetime2 データ型が明示的に定義されている 2 つの列があります。 これらの列は、"期間" 列と呼ばれます。 これらの期間列は、行が変更されるたびに各行の有効期間を記録するためにシステムのみに使用されます。 現在のデータを格納するメインのテーブルは、"現在のテーブル"、または単に "テンポラル テーブル" と呼ばれます。

テンポラル テーブルには、これらの期間列に加え、ミラー化されたスキーマを使用する別のテーブル ("履歴テーブル" という) への参照も含まれています。 システムでは履歴テーブルを使用して、テンポラル テーブルの行が更新または削除されるたびに、行の以前のバージョンを自動的に格納します。 テンポラル テーブルの作成時に、既存の履歴テーブルを指定するか (スキーマ準拠である必要がある)、システムに既定の履歴テーブルを作成させます。

テンポラルである理由

データの実際のソースは動的で、ビジネスの意思決定はアナリストがデータの進化から得ることができる洞察に通常依存しています。 テンポラル テーブルの使用例は次のとおりです。

  • すべてのデータ変更の監査と、必要に応じてのデータの科学捜査の実行
  • 過去の任意の時点でのデータの状態の再構築
  • 長期の傾向の計算
  • 意思決定支援アプリケーションのための緩やかに変化するディメンションの維持
  • 偶発的なデータ変更やアプリケーション エラーからの復旧

テンポラルのしくみ

テーブルのシステム バージョン管理は、現行テーブルと履歴テーブルの 1 組のテーブルとして実装されます。 これらの各テーブル内では、各行の有効期間を定義するために 2 つの追加の datetime2 列が使用されます。

  • 期間開始列: システムにより、この行の開始時間が、通常は ValidFrom 列である列に書き込まれます。

  • 期間終了列: システムにより、この行の終了時間が、通常は ValidTo 列である列に書き込まれます。

現在のテーブルには、各行の ''現在の値'' が含まれています。 履歴テーブルには、存在する場合は各行のそれぞれの以前の値 (''古いバージョン'') と、それが有効であった期間の開始時間と終了時間が含まれています。

テンポラル テーブルのしくみを示す図。

次のスクリプトは、従業員情報を含むシナリオを示しています。

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

詳細については、「システム バージョン管理されたテンポラル テーブルを作成する」を参照してください。

  • Inserts: システムにより、ValidFrom 列の値がシステム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) に設定され、ValidTo 列の値が最大値の 9999-12-31 に割り当てられます。 これは行をオープンとマークします。

  • Updates: システムにより、行の前の値が履歴テーブルに格納され、ValidTo 列の値がシステム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) に設定されます。 これは行をクローズドとマークし、行が有効であった期間が記録されます。 現行テーブルでは、行は新しい値で更新され、システムにより ValidFrom 列には、システム クロックに基づくトランザクションの開始時間 (UTC タイム ゾーン) の値が設定されます。 現行テーブル内の ValidTo 列の更新された行の値は、最大値の 9999-12-31 のままです。

  • Deletes: システムにより、行の前の値が履歴テーブルに格納され、ValidTo 列の値がシステム クロックに基づく現在のトランザクションの開始時間 (UTC タイム ゾーン) に設定されます。 これは行をクローズドとマークし、前の行が有効であった期間が記録されます。 現行テーブルでは、その行は削除されます。 現在のテーブルのクエリではこの行は返されません。 履歴データを処理するクエリのみで、クローズドの行のデータが返されます。

  • Merge: MERGE ステートメントでアクションとして指定されている内容に応じて、まさに最大 3 つのステートメント (INSERTUPDATE、または DELETE、あるいはこれらすべて) が実行されたかのような動作になります。

システムの datetime2 列に記録されている時間は、トランザクション自体の開始時間に基づいています。 たとえば、1 つのトランザクションで挿入されたすべての行の、SYSTEM_TIME 期間の開始に対応する列の UTC 時間は同じになります。

テンポラル テーブルに対してデータ変更クエリを実行すると、列の値が変更されなくても、履歴テーブルにデータベース エンジンが行を追加します。

テンポラル データのクエリ方法

SELECT ... FROM <table> ステートメントには新しい句 FOR SYSTEM_TIME があり、現在および履歴テーブル全体のデータに対してクエリを実行するための 5 つのテンポラル専用のサブ句があります。 この新しい SELECT ステートメントの構文は、1 つのテーブルで直接サポートされており、複数の結合を介して、また複数のテンポラル テーブル上のビューを介して反映されます。

次の図に示すように、5 つのサブ句のいずれかを使用し、FOR SYSTEM_TIME 句を使ってクエリを実行すると、テンポラル テーブルの 履歴データが含まれます。

テンポラルのクエリのしくみを示す図。

次のクエリでは、少なくとも 2021 年 1 月 1 日から 2022 年 1 月 1 日 (上限の境界を含む) の間にアクティブであった、フィルター条件 WHERE EmployeeID = 1000 の従業員の行バージョンが検索されます。

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME では、有効期間がゼロの行 (ValidFrom = ValidTo) は除外されます。

それらの行は、同じトランザクションで同じ主キーに対して複数の更新を実行すると生成されます。 その場合、テンポラル クエリでは、トランザクション前の行バージョンと、トランザクション後の現在の行のみを返します。

それらの行を分析に含める必要がある場合は、履歴テーブルを直接クエリします。

以下の表では、該当行列の ValidFrom はクエリ対象のテーブルの ValidFrom 列の値を示し、ValidTo はクエリ対象のテーブルの ValidTo 列の値を示します。 完全な構文と例については、「FROM 句と JOIN、APPLY、PIVOT」および「システム バージョン管理されたテンポラル テーブル内のデータに対してクエリを実行する」を参照してください。

Expression 該当行 Note
AS OF date_time ValidFrom <= date_time AND ValidTo > date_time 過去の指定された時点では現在であった値を含む行のあるテーブルを返します。 内部的には、テンポラル テーブルとその履歴テーブルの結合が行われます。 結果がフィルター処理されて、date_time パラメーターで指定された時点で有効だった行の値が返されます。 system_start_time_column_name 値が date_time パラメーター値と等しいかそれよりも小さく、system_end_time_column_name 値が date_time パラメーター値より大きい場合に、行の値は有効と見なされます。
FROM start_date_time TO end_date_time ValidFrom < end_date_time AND ValidTo > start_date_time 指定された時間範囲内でアクティブだったすべての行バージョンの値を含むテーブルを返します。FROM 引数の start_date_time パラメーター値の前にアクティブになったか、TO 引数の end_date_time パラメーター値の後にアクティブでなくなったかは関係ありません。 内部的には、テンポラル テーブルとその履歴テーブルの結合が行われます。 結果をフィルター処理すると、指定した時間範囲の中にいつでもにアクティブだったすべての行のバージョンの値を返します。 FROM エンドポイントによって定義されている下限のちょうど境界上でアクティブではなくなった行は含まれず、TO エンドポイントによって定義された上限のちょうど境界上でアクティブになったレコードも含まれません。
BETWEEN start_date_time AND end_date_time ValidFrom <= end_date_time AND ValidTo > start_date_time 返される行のテーブルには end_date_time エンドポイントで定義された上限の境界でアクティブになった行が含まれることを除き、上記の FOR SYSTEM_TIME FROM start_date_time TO end_date_time の説明と同じです。
CONTAINED IN (start_date_time, end_date_time) ValidFrom >= start_date_time AND ValidTo <= end_date_time CONTAINED IN 引数の 2 つの期間値で定義された指定時間範囲内に開かれて閉じられたすべての行バージョンの値を含むテーブルを返します。 行が下位の境界に正確に有効になったまたは上限の境界上だけでアクティブにされているが中断されることでは、含まれています。
ALL すべての行 現行および履歴テーブルに属する行の和を返します。

期間列を非表示にする

明示的に参照しないクエリではこれらの列が返されないように、期間列を非表示にすることを選択できます (たとえば、SELECT * FROM <table> を実行する場合)。

非表示の列を返すには、クエリで非表示の列を明示的に参照する必要があります。 同様に、INSERT および BULK INSERT ステートメントでも、これらの新しい期間列が存在しないかのように続行されます (列値は自動入力されます)。

HIDDEN 句の使用の詳細については、CREATE TABLE と ALTER TABLE に関するページを参照してください。

サンプル

関連するコンテンツ