変更追跡関数を使用した変更の取得

変更追跡関数について

アプリケーションでは、次の関数を使用して、データベースに加えられた変更およびその変更に関する情報を取得することができます。

• CHANGETABLE(CHANGES …) 関数
  変更情報のクエリには、この行セット関数を使用します。この関数は、内部変更追跡テーブルに格納されたデータに対してクエリを実行します。この関数は、変更された行の主キーとその他の変更情報 (該当する操作、更新された列、行のバージョンなど) を含む結果セットを返します。

  CHANGETABLE(CHANGES …) は、最終同期バージョンを引数として受け取ります。最終同期バージョンは、このトピックの例に示されているように、@last_synchronization_version 変数を使用して取得されます。最終同期バージョンのセマンティクスは次のとおりです。

  • 呼び出し元のクライアントは、変更を取得し、最終同期バージョン以前のすべての変更を認識しています。

  • そのため、CHANGETABLE(CHANGES …) は、最終同期バージョンより後で発生したすべての変更を返します。

    次の図は、CHANGETABLE(CHANGES …) を使用して変更を取得する方法を示しています。

    

• CHANGE_TRACKING_CURRENT_VERSION() 関数
  この関数を使用すると、現在のバージョンを取得して、次回の変更クエリの際に使用できます。このバージョンは、最後にコミットされたトランザクションのバージョンを表します。

• CHANGE_TRACKING_MIN_VALID_VERSION() 関数
  この関数を使用すると、クライアントの有効な最小バージョン (CHANGETABLE() から有効な結果を取得するために最低限必要なバージョン) を取得できます。クライアントは、この関数から返される値に対して最終同期バージョンをチェックする必要があります。最終同期バージョンがこの関数から返されたバージョンより小さいと、クライアントは CHANGETABLE() から有効な結果を取得できないため、再初期化が必要になります。

初期データの取得

アプリケーションで初めて変更を取得する前に、クエリを送信して初期データおよび同期バージョンを取得する必要があります。アプリケーションで適切なデータをテーブルから直接取得してから、CHANGE_TRACKING_CURRENT_VERSION() を使用して初期バージョンを取得する必要があります。このバージョンは、初めて変更を取得するときに CHANGETABLE(CHANGES …) に渡されます。

次の例は、初期同期バージョンと初期データセットを取得する方法を示しています。

    -- Obtain the current synchronization version. This will be used next time that changes are obtained.
    SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

    -- Obtain initial data set.
    SELECT
        P.ProductID, P.Name, P.ListPrice
    FROM
        SalesLT.Product AS P

変更追跡関数を使用した変更の取得

テーブルの変更された行およびその変更に関する情報を取得するには、CHANGETABLE(CHANGES…) を使用します。たとえば、次のクエリは、SalesLT.Product テーブルの変更を取得します。

SELECT
    CT.ProductID, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT

通常、クライアントでは、行の主キーだけでなく、行の最新のデータを取得する必要があります。そのため、アプリケーションで CHANGETABLE(CHANGES …) の結果をユーザー テーブルのデータと結合します。たとえば、次のクエリでは、SalesLT.Product テーブルと結合して、Name 列と ListPrice 列の値を取得します。OUTER JOIN が使用されていることに注意してください。これは、ユーザー テーブルから削除された行の変更情報が返されるようにするために必要です。

SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

次回の変更の列挙で使用するバージョンを取得するには、次の例に示すように、CHANGE_TRACKING_CURRENT_VERSION() を使用します。

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()

アプリケーションで変更を取得する際は、次の例に示すように、CHANGETABLE(CHANGES…) と CHANGE_TRACKING_CURRENT_VERSION() の両方を使用する必要があります。

-- Obtain the current synchronization version. This will be used the next time CHANGETABLE(CHANGES...) is called.
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

-- Obtain incremental changes by using the synchronization version obtained the last time the data was synchronized.
SELECT
    CT.ProductID, P.Name, P.ListPrice,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
    CT.SYS_CHANGE_CONTEXT
FROM
    SalesLT.Product AS P
RIGHT OUTER JOIN
    CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
    P.ProductID = CT.ProductID

バージョン番号

変更の追跡が有効になっているデータベースにはバージョン カウンターがあり、変更追跡対象テーブルに変更が加えられるたびに値が増加します。変更された各行には、バージョン番号が関連付けられています。変更のクエリを実行する要求がアプリケーションに送信されると、バージョン番号を提供する関数が呼び出されます。この関数により、そのバージョン以降に行われたすべての変更に関する情報が返されます。変更追跡バージョンは、rowversion データ型の概念といくつかの点で似ている部分があります。

最終同期バージョンの検証

変更に関する情報を保持する期間には制限があります。期間の長さは、ALTER DATABASE の一部として指定できる CHANGE_RETENTION パラメーターで制御されます。

CHANGE_RETENTION に指定された期間によって、すべてのアプリケーションで、データベースから変更を要求する必要がある頻度が決まることに注意してください。last_synchronization_version の値がテーブルの有効な最小同期バージョンより古い場合、そのアプリケーションでは有効な変更の列挙を実行できません。これは、変更情報の一部がクリーンアップされている場合があるためです。アプリケーションで CHANGETABLE(CHANGES …) を使用して変更を取得する前に、CHANGETABLE(CHANGES …) に渡す予定の last_synchronization_version の値を検証する必要があります。last_synchronization_version の値が有効ではない場合は、そのアプリケーションのすべてのデータを再初期化する必要があります。

次の例では、last_synchronization_version の値の有効性をテーブルごとに検証する方法を示します。

    -- Check individual table.
    IF (@last_synchronization_version < CHANGE_TRACKING_MIN_VALID_VERSION(
                                       OBJECT_ID('SalesLT.Product')))
    BEGIN
      -- Handle invalid version and do not enumerate changes.
      -- Client must be reinitialized.
    END

次の例に示すように、last_synchronization_version の値の有効性は、データベースのすべてのテーブルに対してチェックできます。

    -- Check all tables with change tracking enabled
    IF EXISTS (
      SELECT * FROM sys.change_tracking_tables
      WHERE min_valid_version > @last_synchronization_version )
    BEGIN
      -- Handle invalid version & do not enumerate changes
      -- Client must be reinitialized
    END

列追跡の使用

列追跡を使用すると、行全体ではなく、変更された列のみのデータをアプリケーションで取得できます。たとえば、サイズは大きいが変更頻度は低い 1 つ以上の列と、頻繁に変更される他の列で構成されるテーブルのシナリオについて考えてみます。列追跡を使用しない場合、アプリケーションでは行が変更されたことしか判断できないため、大きな列データを含むすべてのデータを同期する必要があります。一方、列追跡を使用すると、アプリケーションでは大きな列データが変更されたかどうかを判断し、変更された場合にのみそのデータを同期できます。

列追跡情報は、CHANGETABLE(CHANGES …) 関数によって返される SYS_CHANGE_COLUMNS 列に表示されます。

列追跡を使用すると、変更されていない列に対して NULL が返されるようにすることができます。NULL に変更できる列の場合は、別々に列を返してその列が変更されたかどうかを示す必要があります。

次の例では、CT_ThumbnailPhoto 列は、変更されていない場合は NULL になります。この列は NULL に変更された場合にも NULL になりますが、アプリケーションでは CT_ThumbNailPhoto_Changed 列を使用して、列が変更されたかどうかを判断できます。

DECLARE @PhotoColumnId int = COLUMNPROPERTY(
    OBJECT_ID('SalesLT.Product'),'ThumbNailPhoto', 'ColumnId')

SELECT
    CT.ProductID, P.Name, P.ListPrice, -- Always obtain values.
    CASE
           WHEN CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) = 1
            THEN ThumbNailPhoto
            ELSE NULL
      END AS CT_ThumbNailPhoto,
      CHANGE_TRACKING_IS_COLUMN_IN_MASK(
                     @PhotoColumnId, CT.SYS_CHANGE_COLUMNS) AS
                                   CT_ThumbNailPhoto_Changed
     CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
     CT.SYS_CHANGE_CONTEXT
FROM
     SalesLT.Product AS P
INNER JOIN
     CHANGETABLE(CHANGES SalesLT.Product, @last_synchronization_version) AS CT
ON
     P.ProductID = CT.ProductID AND
     CT.SYS_CHANGE_OPERATION = 'U'

一貫性のある正しい結果の取得

テーブルの変更されたデータを取得するには、複数の手順を実行する必要があります。特定の問題について考慮して対処しないと、一貫性のない結果や正しくない結果が返される可能性があることに注意してください。

たとえば、Sales テーブルと SalesOrders テーブルに対して行われた変更を取得するには、アプリケーションで次の手順を実行します。

1. CHANGE_TRACKING_MIN_VALID_VERSION() を使用して最終同期バージョンを検証します。

2. CHANGE_TRACKING_CURRENT_VERSION() を使用して、次回の変更の取得の際に使用できるバージョンを取得します。

3. CHANGETABLE(CHANGES …) を使用して Sales テーブルの変更を取得します。

4. CHANGETABLE(CHANGES …) を使用して SalesOrders テーブルの変更を取得します。

データベースで実行される次の 2 つのプロセスが、上記の手順で返される結果に影響する場合があります。

• バックグラウンドでクリーンアップ プロセスが実行され、指定した保有期間より古い変更追跡情報が削除されます。

  クリーンアップ プロセスは、データベースの変更の追跡を構成したときに指定した保有期間を使用する個別のバックグラウンド プロセスです。問題になるのは、最終同期バージョンが検証されてから CHANGETABLE(CHANGES…) の呼び出しが行われるまでの間にクリーンアップ プロセスが実行された場合です。検証の時点で有効だった最終同期バージョンが、変更の取得時には有効ではなくなっている可能性があります。そのため、正しくない結果が返される場合があります。

• 継続的な DML 操作として、Sales テーブルと SalesOrders テーブルで次のような操作が実行されます。

  • CHANGE_TRACKING_CURRENT_VERSION() を使用して次回のバージョンが取得された後に、テーブルに対して変更が行われる可能性があります。そのため、予想よりも多くの変更が返される場合があります。

  • Sales テーブルから変更を取得する呼び出しと、SalesOrders テーブルから変更を取得する呼び出しの間に、トランザクションがコミットされる可能性があります。そのため、SalesOrder テーブルの結果に、Sales テーブルには存在しない外部キー値が含まれる場合があります。

上記の課題を解決するには、スナップショット分離を使用することをお勧めします。これにより、変更情報の一貫性を確保し、バックグラウンドのクリーンアップ タスクに関連する競合状態を回避することができます。スナップショット トランザクションを使用しないと、変更の追跡を使用するアプリケーションの開発にかかる手間が大幅に増えることがあります。

スナップショット分離の使用

-- The database must be configured to enable snapshot isolation.
ALTER DATABASE AdventureWorksLT2008
    SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN
  -- Verify that version of the previous synchronization is valid.
  -- Obtain the version to use next time.
  -- Obtain changes.
COMMIT TRAN

スナップショット分離の使用に代わる方法

スナップショット分離の使用に代わる方法がありますが、すべてのアプリケーション要件を満たしていることを確認するための作業が必要になります。last_synchronization_version が有効であり、クリーンアップ プロセスでデータが削除されていないことを変更の取得前に確認するには、次の手順を実行します。

1. CHANGETABLE() の呼び出し後に last_synchronization_version をチェックします。

2. CHANGETABLE() を使用して変更を取得する各クエリで、その一部として last_synchronization_version をチェックします。

次回の列挙の同期バージョンが取得された後に変更が行われる可能性があります。この状況に対処するには、2 つの方法があります。使用するオプションは、アプリケーションおよび各方法の副作用への対処方法によって異なります。

• 新しい同期バージョンよりバージョンが大きい変更を無視します。

  この方法の副作用として、新しい同期バージョンより前に行が作成または更新された場合、新しい行や更新された行がスキップされ、後で更新されます。新しい行がある場合、作成された別のテーブルにスキップされた行を参照する行があると、参照整合性の問題が発生する可能性があります。更新された既存の行がある場合、その行はスキップされ、次回まで同期されません。

• 新しい同期バージョンよりバージョンが大きい変更も含め、すべての変更を含めます。

  新しい同期バージョンよりバージョンが大きい行は、次回の同期で再度取得されます。アプリケーションでは、このことを想定して対処する必要があります。

上記の 2 つのオプションに加え、操作に応じて両方を組み合わせた方法を検討することもできます。たとえば、アプリケーションによっては、次回の同期バージョンより新しい変更 (行の作成または削除) は無視し、更新は無視しないようにすることが最適な場合もあります。

注
変更の追跡 (または任意のカスタムの追跡メカニズム) を使用する場合にアプリケーションで使用する方法を選択する際には、十分な分析が必要です。したがって、スナップショット分離を使用した方がはるかに簡単です。

関連項目

タスク

概念

変更履歴