システム バージョン管理されたテンポラル テーブル内のデータに対してクエリを実行する

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

テンポラル テーブルのデータの最新 (現在) の状態を取得するときは、テンポラル以外のテーブルをクエリするときと同じ方法でクエリできます。 PERIOD 列が非表示ではない場合は、それらの値が SELECT * クエリで表示されます。 PERIOD 列を HIDDEN として指定した場合、それらの値は SELECT * クエリでは表示されません。 PERIOD 列が非表示の場合、PERIOD 列の値を返すには、SELECT 句でそれらの列を明示的に参照する必要があります。

任意の種類の時間ベースの分析を実行するには、新しい FOR SYSTEM_TIME 句を使用し、テンポラル固有の 4 つのサブ句を指定して、現在のテーブルと履歴テーブルにわたってデータをクエリします。 これらの句の詳細については、「テンポラル テーブルと FROM 句と JOIN、APPLY、PIVOT」を参照してください。

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>)
• ALL

FOR SYSTEM_TIME は、クエリで各テーブルに対して個別に指定できます。 共通テーブル式、テーブル値関数、ストアド プロシージャの中で使用できます。 テンポラル テーブルでテーブルの別名を使用する場合は、テンポラル テーブル名と別名の間に FOR SYSTEM_TIME 句が含まれている必要があります (「AS OF サブ句を使用した特定時点のクエリ」の 2 つ目の例を参照してください)。

AS OF サブ句を使用した特定時点のクエリ

過去の特定時点におけるデータの状態を再構築する必要がある場合は、AS OF サブ句を使用します。 PERIOD 列の定義で指定されている datetime2 型の精度でデータを再構築できます。

AS OF サブ句を定数リテラルまたは変数と共に使用して、時間条件を動的に指定できます。 指定した値は UTC 時刻として解釈されます。

この最初の例では、過去の特定の日時 (AS OF) における dbo.Department テーブルの状態が返されます。

-- State of entire table AS OF specific date in the past
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

この 2 番目の例では、行のサブセットの値が 2 つの時点について比較されます。

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(DAY, -1, SYSUTCDATETIME());

-- Comparison between two points in time for subset of rows
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

テンポラル クエリにおける AS OF サブ句でのビューの使用

複雑な特定時点分析が必要なときは、ビューが役に立ちます。 一般的な例は、過去 1 か月の値を使用して今日のビジネス レポートを生成する場合です。

通常、ユーザーは外部キー リレーションシップを持つ多数のテーブルを含む正規化されたデータベース モデルを使用します。 すべてのテーブルは個別に独立したパターンで変化するので、その正規化されたモデルのデータが過去の特定の時点においてどのようなものになるかを調べるのは難しい場合があります。

このような場合に最善の方法は、ビューを作成し、AS OF サブ句をビュー全体に適用することです。 この方法を使用すると、SQL Server によって、ビューの定義に含まれるすべてのテンポラル テーブルに AS OF 句が透過的に適用されるので、特定時点分析からデータ アクセス層のモデルを切り離すことができます。 さらに、テンポラル テーブルと非テンポラル テーブルを同じビューに含めることができます。AS OF はテンポラル テーブルに対してのみ適用されます。 ビューでテンポラル テーブルが 1 つも参照されていない場合、テンポラル クエリ句をテーブルに適用するとエラーで失敗します。

次のサンプル コードでは、次の 3 つのテンポラル テーブル (Department、CompanyLocation、LocationDepartments) を結合するビューを作成します。

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

AS OF サブ句と datetime2 リテラルを使用して、ビューに対してクエリを実行できます。

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

一定期間における特定の行への変更のクエリ

テンポラル サブ句 FROM ... TO、BETWEEN ... AND、CONTAINED IN は、現在のテーブル内の特定の行に対するすべての変更履歴を取得する必要がある場合に、役に立ちます (データ監査とも呼ばれます)。

最初の 2 つのサブ句は指定された期間と重なる行のバージョンを返します (つまり、指定された期間より前に開始し、期間の後で終了するバージョン) が、CONTAINED IN は指定された期間の範囲内に存在するものだけを返します。

最新ではない行のバージョンのみを検索する場合は、最高のクエリ パフォーマンスを実現するために、履歴テーブルのクエリを直接実行する必要があります。 何の制限もなしに現在と履歴のデータをクエリする必要がある場合は、ALL を使用します。

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

関連するコンテンツ

• テンポラル テーブル
• FROM 句と JOIN、APPLY、PIVOT (Transact-SQL)
• システム バージョン管理されたテンポラル テーブルを作成する
• システム バージョン管理のテンポラル テーブルのデータを変更する
• システム バージョン管理されたテンポラル テーブルのスキーマを変更する
• システム バージョン管理されたテンポラル テーブルでシステム バージョン管理を停止する