テンポラルテーブルの使用シナリオ

対象者:SQL Server 2016 (13.x) およびそれ以降のバージョン Azure SQL DatabaseAzure SQL Managed Instance SQL Database in Microsoft Fabric

システムバージョン管理されたテンポラルテーブルは、データの変更履歴を追跡する必要があるシナリオに便利です。生産性が高まるので、次の用途にテンポラルテーブルを使用することをお勧めします。

データ監査

重要な情報を格納するテーブルでは、システムバージョン管理によるテンポラルテーブルを使用して、何がいつ変更されたかを追跡し、任意の時点でデータフォレンジクスを実行できます。

開発サイクルの初期段階でデータ監査シナリオを計画するために、時間テーブルを活用しましょう。必要なときに既存のアプリケーションやソリューションにデータ監査を追加できます。

次の図に、現在 (青) と履歴行バージョン (グレー) のデータサンプルを含む Employee テーブルのシナリオを示します。

図の右側部分は、時間軸上の行バージョンと、 SYSTEM_TIME 時間テーブル上で異なるクエリ方式で選択する行を、節の有無にかかわらず示しています。

データ監査用に新しいテーブルでシステムバージョン管理を有効にする

データの監査が必要な情報を特定したら、データベーステーブルをシステムバージョン管理されたテンポラルテーブルとして作成します。以下の例は、仮想の人事データベースにある Employee というテーブルのシナリオを示しています。

CREATE TABLE 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 (2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

時間システムバージョンテーブルを作成するためのさまざまなオプションは「 Create a system-versioned temporal table」で説明されています。

データ監査用に既存のテーブルでシステムバージョン管理を有効にする

既存のデータベースのデータを監査する必要がある場合、ALTER TABLE を使用し、非テンポラルテーブルをシステムバージョン管理テーブルにします。アプリケーションの変更を壊さないように、システムバージョン付き時間テーブルの作成で説明されているように、期間列を追加してください。HIDDEN。

次の例では、仮定の HR データベース内の既存の Employee テーブルで、システムバージョン管理を有効にする方法について説明します。 2 つの手順で、Employee テーブルのシステムバージョン管理を有効にします。最初に、新しい期間列が HIDDEN として追加されます。次に、既定の履歴テーブルが作成されます。

ALTER TABLE Employee
ADD
    ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Employee
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

Important

datetime2 データ型の精度は、ソーステーブルでもシステムバージョン管理された履歴テーブルと同じである必要があります。

前のスクリプトを実行した後、履歴テーブルがすべてのデータ変更を透過的に収集します。典型的なデータ監査のシナリオでは、関心のある期間内に個々の行に適用されたすべてのデータ変更を照会します。この用途に効果的に対応する、クラスター化された行ストア B ツリーを持つ既定の履歴テーブルが作成されます。

Note

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

データ分析の実行

上記のいずれかの方法を使用してシステムバージョン管理を有効にすれば、データの監査はクエリを 1 回のみ実行すればできます。次のクエリでは、Employee テーブルのうち、少なくとも 2021 年 1 月 1 日から 2022 年 1 月 1 日 (上限の境界を含む) の間にアクティブであった 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 BETWEEN...AND を FOR SYSTEM_TIME ALL で置換すると、その特定の従業員について、データ変更の履歴全体が分析されます。

SELECT *
FROM Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

その期間のみアクティブであった行バージョンを探す場合 (それ以外は含みません)、CONTAINED IN を使用します。このクエリは履歴テーブルのみに対してクエリを行うので、効率的です。

SELECT *
FROM Employee FOR SYSTEM_TIME
    CONTAINED IN ('2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000')
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

最後に、監査のシナリオによっては、過去のどの時点でテーブル全体がどのような状態だったかを確認したいかもしれません:

SELECT *
FROM Employee FOR SYSTEM_TIME
    AS OF '2021-01-01 00:00:00.0000000';

システムバージョン管理されたテンポラルテーブルでは、期間列の値は UTC タイムゾーンで格納されますが、データのフィルター処理と結果の表示の両方については、ローカルのタイムゾーンで作業する方がより便利な場合があります。以下のコードサンプルは、フィルタリング条件を適用する方法を示しています。これは現地のタイムゾーンで指定され、その後UTCに変換されます AT TIME ZONE:

/* Add offset of the local time zone to current time*/
DECLARE @asOf AS DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, -9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
       [Name],
       Position,
       Department,
       [Address],
       [AnnualSalary],
       ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
       ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

AT TIME ZONE は、システムバージョン管理されたテーブルを使用するすべてのシナリオで役立ちます。

FOR SYSTEM_TIMEを持つテンポラル句で指定されたフィルター条件は SARGable です。

Note

リレーショナルデータベースにおける SARGable という用語は、Search ARGumentable な述語を指し、これはインデックスを使用することでクエリの実行を高速化できることを意味します。詳細については、 SQL Server と Azure SQL インデックスのアーキテクチャと設計ガイドを参照してください。

履歴テーブルを直接照会する場合は、フィルターを <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC'の形で指定して、フィルタリング条件もSARG可能であることを確認しましょう。

期間列に AT TIME ZONE を適用すると、SQL Server はテーブルまたはインデックススキャンを実行します。これはコストがかかる可能性があります。クエリではこの種の条件を避けてください:

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition。

詳細については、「システムバージョン管理されたテンポラルテーブル内のデータに対してクエリを実行する」を参照してください。

特定の時点の分析 (タイムトラベル)

個々の記録の変化に焦点を当てるのではなく、タイムトラベルのシナリオはデータセット全体が時間とともにどのように変化するかを示します。時には、タイムトラベルには独立したペースで変化する複数の関連する時間表が含まれることがあり、それらについて分析したいことがあります。

重要な指標の過去および現在のデータでの傾向
(昨日や 1 か月前など) 過去の任意の "時点" のデータ全体の正確なスナップショット
(1 か月前対 3 か月前など) 関心のある 2 つの時点の違い

多くの現実世界のシナリオではタイムトラベル分析が必要です。この使用状況を説明するために、自動生成履歴を持つオンライントランザクション処理(OLTP)を見てみましょう。

自動生成されたデータ履歴を使用する OLTP

トランザクション処理システムでは、時間の経過とともに重要なメトリックがどのように変わるかを分析できます。理想的には、履歴解析がOLTPアプリケーションのパフォーマンスを損なうべきではありません。なぜなら、最新のデータ状態へのアクセスが最小限の遅延とデータロックで行われる必要があるからです。システムバージョン管理されたテンポラルテーブルを使用して、現在のデータとは別に、メインの OLTP ワークロードへの影響を最小限に抑えながら、後で分析するために変更の完全な履歴を透過的に保持することができます。

SQL ServerやAzure SQL Managed Instanceで高トランザクション処理ワークロードを扱う場合は、メモリ最適化されたテーブルを備えたシステムバージョン対応の時間テーブルを使用することを推奨します。これにより、現在のデータをメモリ内に保存し、変更履歴をコスト効率よくディスクに保存できます。

履歴テーブルには、次の理由から、クラスター化列ストアインデックスを使用することお勧めします。

クラスター化列ストアインデックスのクエリのパフォーマンスは、典型的な傾向の分析に有効です。
履歴テーブルにクラスター化列ストアインデックスがあり、OLTP のワークロードが高い場合、メモリが最適化されたテーブルでのデータのフラッシュタスクは、最も高いパフォーマンスを発揮します。
クラスター化列ストアインデックスは、特にすべての列が同時に変更されるとは限らないシナリオで優れた圧縮を提供します。

インメモリOLTPで時間テーブルを使うことで、データセット全体をメモリ内に保持する必要が減り、ホットデータとコールドデータの区別が容易になります。

このカテゴリに適合する実際のシナリオの例は、特に在庫管理や為替取引です。

以下の図は在庫管理に用いられる簡略化されたデータモデルを示しています:

以下のコード例は、 ProductInventory をメモリ内のシステムバージョン付き時時テーブルとして作成し、履歴テーブルにクラスタ化されたカラムストアインデックスを付けます(これはデフォルトで作成される行ストアインデックスの代わりです):

Note

データベースでメモリ最適化テーブルの作成が許可されていることを確認します。「メモリ最適化テーブルおよびネイティブコンパイルストアドプロシージャの作成」を参照してください。

USE TemporalProductInventory;
GO

BEGIN

    --If the table is system-versioned, set SYSTEM_VERSIONING to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
            SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];

END
GO

CREATE TABLE [dbo].[ProductInventory]
(
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (ProductId, LocationId),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
    ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

前述のモデルでは、在庫を管理する手順は次のようになります。

CREATE PROCEDURE [dbo].[spUpdateInventory] (
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    UPDATE dbo.ProductInventory
        SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId;

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location
    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
        BEGIN
            SET @quantityIncrement = 0;
        END

        INSERT INTO [dbo].[ProductInventory]
        (
            [ProductId],
            [LocationID],
            [Quantity]
        )
        VALUES (
            @productId,
            @locationId,
            @quantityIncrement
        );
    END
END;

spUpdateInventory ストアドプロシージャによって、在庫に新しい製品が挿入されるか、特定の場所の製品の数量が更新されます。ビジネスロジックはシンプルで、テーブル更新を通じて Quantity フィールドを増減・減分することで常に最新の状態を正確に保つことに重点を置いています。一方、システムバージョン制御のテーブルは、以下の図に示されるようにデータに履歴の次元を透過的に加えます。

これで、ネイティブにコンパイルされたモジュールから最新の状態を効率的にクエリできます:

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    SELECT ProductId,
           LocationID,
           Quantity,
           ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId;
END;
GO

EXECUTE [dbo].[spQueryInventoryLatestState];

次の例に示すとおり、FOR SYSTEM_TIME ALL 句を使用すると、データの経時的な変化の分析が簡単になります。

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory AS
    SELECT ProductId,
           LocationId,
           Quantity,
           ValidFrom,
           ValidTo
    FROM [dbo].[ProductInventory] FOR SYSTEM_TIME ALL;
GO

SELECT *
FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

次の図には、Power Query、Power BI または類似のビジネスインテリジェンスツールに前述のビューをインポートすることにより、簡単にレンダリングできる 1 つの製品のデータ履歴を示しています。

このシナリオでは、時間表を使って過去の任意の時点 AS OF インベントリの状態を再構築したり、異なる時間点に属するスナップショットを比較したりといった他のタイプのタイムトラベル分析を行うことができます。

この利用シナリオでは、 Product テーブルや Location テーブルを時空テーブルに拡張し、 UnitPrice や NumberOfEmployeeの変化履歴を後で分析することも可能です。

ALTER TABLE Product
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE Product
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location]
ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT DFValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE [Location]
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

データモデルは複数の時間テーブルを含むため、 AS OF 分析のベストプラクティスは、関連するテーブルから必要なデータを抽出し、ビューに FOR SYSTEM_TIME AS OF を適用することで、データモデル全体の状態を再構築する作業が大幅に簡素化されます。

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
       PrInv.LocationId,
       p.ProductName,
       l.LocationName,
       PrInv.Quantity,
       p.UnitPrice,
       l.NumberOfEmployees,
       p.ValidFrom AS ProductStartTime,
       p.ValidTo AS ProductEndTime,
       l.ValidFrom AS LocationStartTime,
       l.ValidTo AS LocationEndTime,
       PrInv.ValidFrom AS InventoryStartTime,
       PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
    INNER JOIN dbo.Product AS p
        ON PrInv.ProductId = p.ProductID
    INNER JOIN dbo.Location AS l
        ON PrInv.LocationId = l.LocationID;
GO

SELECT *
FROM vw_ProductInventoryDetails
    FOR SYSTEM_TIME AS OF '2022-01-01';

次のスクリーンショットは、SELECT クエリ用に生成された実行プランを示しています。これは、データベースエンジンによって、テンポラルな関係を処理するときのすべての複雑が処理されることを示しています。

以下のコードを使って、2つの時点(1日前と1か月前)の製品在庫の状態を比較してください:

DECLARE @dayAgo AS DATETIME2 = DATEADD(DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo AS DATETIME2 = DATEADD(MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
       inventoryDayAgo.ProductName,
       inventoryDayAgo.LocationName,
       inventoryDayAgo.Quantity AS QuantityDayAgo,
       inventoryMonthAgo.Quantity AS QuantityMonthAgo,
       inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
       inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
     INNER JOIN vw_ProductInventoryDetails FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
         ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

異常検出

異常検出、または 外れ値検出は、予想されるパターンやデータセット内の他の項目に合致しない項目を特定します。システムバージョン制御された時間テーブルを用いて、時間クエリを用いて特定のパターンを素早く特定することで、周期的または不規則に発生する異常を検出できます。異常とみなされるものは、収集するデータの種類やビジネスの論理によって異なります。

次の例は、販売数の "急増" を検出する単純化されたロジックです。購入品の履歴を収集するテンポラルテーブルで作業しているとします。

CREATE TABLE [dbo].[Product]
(
    [ProdID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] VARCHAR (100) NOT NULL,
    [DailySales] INT NOT 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].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

次の図は購入を時間順に示しています。

通常の日は購入品数にはあまり変動がないことを前提に、次のクエリでは単一の外れ値が識別されます。つまり、周りのサンプルは大きくは変わらない中 (20% 未満)、すぐ隣のものと比較して大きく異なる (2 倍) サンプルが識別されます。

WITH CTE (ProdId, PrevValue, CurrentValue, NextValue, ValidFrom, ValidTo)
AS (SELECT ProdId,
           LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
           DailySales,
           LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
           ValidFrom,
           ValidTo
    FROM Product FOR SYSTEM_TIME ALL)
SELECT ProdId,
       PrevValue,
       CurrentValue,
       NextValue,
       ValidFrom,
       ValidTo,
       ABS(PrevValue - NextValue) / CONVERT (FLOAT, (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END)) AS PrevToNextDiff,
       ABS(CurrentValue - PrevValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END)) AS CurrentToPrevDiff,
       ABS(CurrentValue - NextValue) / CONVERT (FLOAT, (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (CASE WHEN NextValue > PrevValue THEN PrevValue ELSE NextValue END) < 0.2
      AND ABS(CurrentValue - PrevValue) / (CASE WHEN CurrentValue > PrevValue THEN PrevValue ELSE CurrentValue END) > 2
      AND ABS(CurrentValue - NextValue) / (CASE WHEN CurrentValue > NextValue THEN NextValue ELSE CurrentValue END) > 2;

Note

この例は意図的に単純化しています。運用シナリオでは、一般的なパターンに従わないサンプルを特定するために、高度な統計方法を使用する可能性があります。

緩やかに変化するディメンション

通常、データウェアハウスのディメンションには、エンティティに関する地理的な場所、顧客、または製品など、比較的静的なデータが含まれます。ただし、一部のシナリオでは、ディメンションテーブル内のデータも追跡する必要があります。次元の修正ははるかに少なく、予測不能で、またファクトテーブルに適用される定期的な更新スケジュール外で行われるため、これらの次元テーブルはゆっくり変化する次元(SCD)と呼ばれます。

変化の歴史の保存方法に基づいて、ゆっくりと変化する次元にはいくつかのカテゴリーがあります。

分析コードタイプ	詳細情報
型 0	履歴は保存されません。ディメンション属性は元の値です。
型 1	ディメンション属性には最新の値が反映されます (前の値は上書きされます)。
型 2	ディメンションメンバーのすべてのバージョンは、通常は有効期間を示す列と共にテーブルに別の行に示されます。
型 3	同じ行の追加の列を使用して、選択された属性の限られた履歴を保持する
型 4	元のディメンションテーブルには最新 (現在) のディメンションメンバーのバージョンを保持しながら、別のテーブルに履歴を保持します。

SCD 戦略を選択した場合、ディメンションテーブルは ETL (Extract-Transform-Load) 層によって正確に維持されますが、通常はより複雑なコードと余分なメンテナンスが必要になります。

システムのバージョン制御された時間テーブルを使うことでコードの複雑さを大幅に下げることができます。なぜならデータの履歴が自動的に保存されるからです。 2 つのテーブルを使用して実装されるテンポラルテーブルは、タイプ 4 の SCD に最も近いものです。しかし、テンポラルクエリでは現在のテーブルしか参照できないので、タイプ 2 の SCD の使用を計画している環境でもテンポラルテーブルを検討できます。

通常の次元をSCDに変換するには、新しい次元を作成するか、既存の次元を改変してシステムバージョン対応の時間テーブルにすることができます。既存の次元テーブルに過去のデータがある場合は、別のテーブルを作成し、過去データをそこに移動させ、現在(実際の)次元バージョンは元の次元テーブルに保持してください。次いで ALTER TABLE 構文を使用し、ディメンションテーブルを事前定義された履歴テーブルを持つシステムバージョン管理されたテンポラルテーブルに変換します。

以下の例はこのプロセスを示しており、 DimLocation 次元テーブルにはすでに ValidFrom と ValidTo が datetime2 の非nullable カラムとして存在し、ETL プロセスがそれらを埋め込むことを前提としています。

閉じた行バージョンを新しい履歴テーブルに移動させる:

SELECT *
INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

クラスタ化されたカラムストアインデックスを作成しましょう。これはデータウェアハウスのシナリオで良い選択肢です。
```
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory
    ON DimLocationHistory;
```
DimLocationから以前のバージョンを削除し、これが時間システムバージョン設定の現在のテーブルとなります。
```
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
```

句点の定義を追加:

ALTER TABLE DimLocation
    ADD PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

システムのバージョン管理を有効にし、履歴テーブルを DimLocationに割り当てます:
```
ALTER TABLE DimLocation
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));
```

作成後のデータウェアハウスロードプロセス中にSCDを維持するために追加のコードは必要ありません。

以下の図は、2つのSCD(DimLocation と DimProduct)と1つのファクトテーブルを含む基本的なシナリオで時間テーブルをどのように使えるかを示しています。

レポートで以前のSCDを使うには、クエリの調整を効果的に行う必要があります。たとえば、過去 6 か月間の売上額合計と 1 人当たりが販売した製品の平均数を計算したいとします。両方のメトリックでは、ファクトテーブルとディメンションのデータに相関関係がある必要があります。分析に重要なその属性 (DimLocation.NumOfCustomers、DimProduct.UnitPrice) に変更がある可能性があります。

次のクエリによって、必要なメトリックが正しく計算されます。

DECLARE @now AS DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo AS DATETIME2;

SET @sixMonthsAgo = DATEADD(month, -12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
       DimLocation_History.LocationId,
       SUM(f.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
       AVG(f.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales AS f
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimLocation FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
         ON DimLocation_History.LocationId = f.LocationId
        AND f.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
     /* find corresponding record in SCD history in last 6 months, based on matching fact */
     INNER JOIN DimProduct FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
         ON DimProduct_History.ProductId = f.ProductId
        AND f.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE f.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

Considerations

SCDにシステムバージョン制御された時間テーブルを使用することは、データベーストランザクション時間に基づいて計算された有効期間がビジネスロジックに合う場合に問題ありません。データの読み込みに大きな遅延がある場合、取引時間が許容できない場合があります。

既定では、システムバージョン管理されたテンポラルテーブルでは、読み込み後の履歴データは変更できません (SYSTEM_VERSIONING を OFF に設定した後に履歴を変更できます)。履歴データの変更が定期的に行われる場合は、これが制限になる可能性があります。

時間システムバージョン対応テーブルは、任意の列変更時に行バージョンを生成します。特定の列変更で新しいバージョンを抑制したいなら、その制限をETLロジックに組み込む必要があります。

SCDテーブルに多くの過去行が含まれると予想される場合は、履歴テーブルの主な保存オプションとしてクラスタ化されたカラムストアインデックスの使用を検討してください。列ストアインデックスを使用すると、履歴テーブルのフットプリントが減少し、分析クエリが高速化されます。

データの行レベルでの破損を修復する

システムバージョン管理されたテンポラルテーブルの履歴データを使用すると、個々の行を以前にキャプチャした任意の状態に迅速に修復できます。テンポラルテーブルのこのプロパティは、影響を受けた行を探せる場合や、不要なデータ変更が行われた時間がわかっている場合に便利です。この知識により、バックアップを処理することなく効率的に修復を実行できます。

この方法には、いくつかの利点があります。

修復のスコープを正確に制御できます。影響を受けていないレコードは最新の状態に維持される必要があり、これは多くの場合、非常に重要な要件になります。
効率的に操作でき、そのデータを使用するすべてのワークロードのためにデータベースをオンライン状態に維持できます。
修復操作自体にバージョンが付けられます。修理作業の監査記録があるので、必要に応じて後で何が起こったか分析できます。

修理作業は比較的簡単に自動化できます。以下のコード例は、データ監査シナリオで使用されるテーブルのデータ修復を行うストアドプロシージャ Employee 示しています。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord (
    @EmployeeID INT,
    @versionNumber INT = 1
)
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID
        ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999
          AND Employee.EmployeeID = @EmployeeID)

/* Update current row using N-th row version from history
(default is 1, that is, the last version) */
UPDATE Employee
    SET [Position] = h.[Position],
        [Department] = h.Department,
        [Address] = h.[Address],
        AnnualSalary = h.AnnualSalary
FROM Employee AS e
     INNER JOIN History AS h
         ON e.EmployeeID = h.EmployeeID
        AND RN = @versionNumber
WHERE e.EmployeeID = @EmployeeID;

このストアドプロシージャでは、@EmployeeID と @versionNumber を入力パラメーターとして受け取ります。デフォルトで行の状態を履歴の最後のバージョン(@versionNumber = 1)に復元します。

以下の図は、プロシージャ呼び出しの前後の行の状態を示しています。赤い長方形は現在の行で誤っているバージョンを示し、緑の長方形は履歴の正しいバージョンを示しています。

EXECUTE sp_RepairEmployeeRecord
    @EmployeeID = 1,
    @versionNumber = 1;

この修復のストアドプロシージャは、行のバージョンではなく、正確なタイムスタンプを受け入れるように定義できます。指定された時点 (つまり、AS OF の時点) でアクティブであった任意のバージョンに行を復元します。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf (
    @EmployeeID INT,
    @asOf DATETIME2
)
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
    SET [Position] = History.[Position],
        [Department] = History.Department,
        [Address] = History.[Address],
        AnnualSalary = History.AnnualSalary
FROM Employee AS e
     INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
         ON e.EmployeeID = History.EmployeeID
WHERE e.EmployeeID = @EmployeeID;

次の図では、同じデータサンプルを時間条件で復元するシナリオを示しています。ハイライトは @asOf パラメータ、指定された時点で実際に存在していた履歴で選択された行、そして修復操作後の現在のテーブルの新しい行バージョンです。

データ修正は、データウェアハウスおよびレポートシステムで自動的にデータを読み込む際の一部にできます。新しく更新された値が正しくない場合、多くのシナリオでは、履歴から以前のバージョンを復元することで十分に対応できます。次の図では、これを自動化する手順を示します。

プロセスを自動化する方法を示す図。

フィードバック

このページはお役に立ちましたか?

Last updated on 2026-08-18

テンポラルテーブルの使用シナリオ

データ監査

データ監査用に新しいテーブルでシステム バージョン管理を有効にする

データ監査用に既存のテーブルでシステム バージョン管理を有効にする

データ分析の実行

特定の時点の分析 (タイム トラベル)

自動生成されたデータ履歴を使用する OLTP

異常検出

緩やかに変化するディメンション

Considerations

データの行レベルでの破損を修復する

関連するコンテンツ

フィードバック

その他のリソース

データ監査用に新しいテーブルでシステムバージョン管理を有効にする

データ監査用に既存のテーブルでシステムバージョン管理を有効にする

特定の時点の分析 (タイムトラベル)