SQL Serverの変更追跡の概要

SQL Serverの変更追跡は、テーブルの挿入、更新、および削除を監視するための柔軟で使いやすいテク この記事では、SQL Serverでの変更追跡の開始について説明し、その開始方法の例を示します。

SQL Serverでの変更の追跡

変更の追跡は、変更の追跡によって監視されるテーブルで挿入、更新、および削除された行を追跡するた 変更の追跡はSQL Server2008で最初に登場し、以降すべてのバージョンに導入されています。 さらに良いことに、変更の追跡は、無料のexpressエディションであっても、SQL Serverのすべてのエディションで利用可能です。

一言で言えば、ここでそれがどのように動作するかです: 変更追跡が有効になっているテーブルでは、各行への正味の変更が内部的に追跡され、変更追跡機能を介してアクセスできます。 各変更にはバージョンIDが添付されており、挿入、更新、または削除されるすべての行に新しいバージョンIDがあります。 変更追跡バージョンは、そのデータベース内の最新の変更IDを反映する8バイトの整数(BIGINT)です。 変更追跡バージョンはテーブル固有のものではないことに注意することが重要です–各データベース内の追跡されたテーブルのDML操作はバージョン番号を増 バージョン番号は常に連続していますが、変更の追跡が有効になっているテーブルが複数ある場合、単一のテーブル内で連続しているとは限りません。

変更の追跡はデータベースレベルで有効になっています。 その後、監視される各テーブルを変更追跡に個別に参加させる必要があります。 これは、変更追跡内のDML操作をレポートするために使用される行レベルの識別子であるためです。 テーブルレベルで変更の追跡を有効にすると、最新の更新で変更された列を追跡することができ、変更された内容をより明確に把握できます。

一度設定すると、変更の追跡を使用するのは比較的簡単なプロセスです。 特にCHANGE_TRACKING_CURRENT_VERSION()とCHANGETABLE()は、変更の追跡で現在のバージョンスタンプをチェックし、最近の変更のリストを取得するために使用できるいくつかの関数があります。 私はこれらの機能の両方をすぐに説明します。

変更の追跡は監査ログではありません

変更の追跡を記述するためにauditまたはloggingという言葉を使用しないように注意します。 私は明確にしましょう：これは完全なロギングメカニズムではありません。 変更履歴はまったく追跡されません–変更の追跡は、変更が発生したという事実のみを報告しますが、バージョン履歴は保持されません。 IDが1234のデータ行の場合を考えてみましょう。 その行が挿入され、5回更新されてから削除されます。 変更の追跡では、挿入、更新、および削除の履歴は表示されません; むしろ、行ID1234が削除されたという正味の変更のみが報告されます。 ロードプロセスで（すべての変更の差分ではなく）各変更の詳細なログ履歴が必要な場合は、変更データキャプチャのようなものを使用する必要があり

SQL Serverでの変更追跡の設定

テーブルレベルの変更追跡の有効化は、二段階のプロセスです。 まず、データベースで有効にする必要があります。 これは、変更追跡タブのデータベースプロパティのUIを使用して行うことができます。

図に示すように、データベースで変更追跡を有効にするときに構成することはあまりありません。 変更追跡の値をTrueに設定するだけで、そのデータベースの変更追跡を設定できます。 必要に応じて、保持期間の値を調整することもできます。 デフォルト値は2日ですが、この例では代わりに14日を使用するようにオーバーライドしました。 ほとんどのUI操作と同様に、同じことを行うためのT-SQLコマンドがあります。 このデータベースで変更の追跡を設定するコマンドは以下のとおりです。

この手順の後、変更の追跡は有効になりますが、まだ何も追跡されていません。 各テーブルを追跡するには、引き続き有効にする必要があります。 テーブルプロパティUIはこれを非常に簡単にします。

次のように、変更追跡値をTrueに変更するだけで、このテーブルの変更追跡が有効になります。 この例では、更新中に変更された列を追跡することも選択しました（これについては少し詳しく説明します）。

上記の最後のステップは、変更の追跡で追跡される各テーブルに対して繰り返されます。 変更の追跡が有効になると、そのテーブルへの変更(挿入、更新、または削除)は変更追跡キャッシュに格納されます。

変更追跡の設定

上記の例では、これらのDML操作用に生成された変更追跡データにアクセスする方法を示すために、いくつかのデータを挿入、更新、およ 参照のために、ここにテーブル構造があります。

UIを使用して単一のテーブルの変更追跡を有効にする方法を以前に示しました。 より簡単に再現できるので、このタスクにはT-SQLを使用することをお勧めします。 上記で作成したテーブルの変更追跡を有効にするには、次のようにします。

変更追跡は、バージョンIDを使用して追跡されたテーブルの現在のバージョンを追跡することを前述したことを思い出してください。 そのバージョンIDは、変更を検出するためのタイムラインマーカーです。 その値を取得するには、非常に単純な関数CHANGE_TRACKING_CURRENT_VERSION()があります。 以下に示すように使用されます。私のテストシステムでは、この値は470です（これを書く前にいくつかのテストを実行したので）。 これが出発点であり、この時点以降に行われた変更は新しいバージョン番号をトリガーします。 その値をメモし、上記の表にいくつかの変更を加えます。 私は変更の追跡が挿入を表示する方法を示すために行の一握りを挿入します。これらの6行を挿入した後、CHANGE_TRACKING_CURRENT_VERSION（）値を再度確認し、値が476になったことがわかります。 それは私が期待しているものである、挿入された行ごとに6–one増加しています。

変更追跡関数の使用

次に、変更追跡関数CHANGETABLE()を使用して、このテーブルに正味の変更を表示します。

これを打破するには:

• CHANGETABLEは、変更追跡に格納されている変更のリストを返すテーブル値システム関数です
• CHANGESは、指定されたバージョン
• @verがバージョン番号を格納するために設 CHANGETABLEは、このバージョン以降の変更を反映したすべての結果を返します。 私が行ったように変数を使用するか、スカラー数を渡すだけでよいことに注意してください（ここでリテラル470を使用すると同じことが達成されます)

上記のコードを実行すると、次の結果セットが表示されます。

これは、挿入および/または更新のバージョン、操作（それぞれ挿入、更新、または削除のI、U、またはD）、更新操作の列マ CHANGETABLE（）はテーブルを返すため、この結果セットを元のテーブルに簡単に結合して、そのテーブルの現在のデータと一緒に変更操作を確認できます。

これは、更新操作のために少し異なります。 次に、updateステートメントを実行しますが、最初に、変更追跡の現在のバージョン（まだ476です）に注意します。

テーブル内の2つの行を更新するupdate文が表示されます:上からCHANGETABLE（）コードを実行すると、より最近の変更追跡バージョン（476）を出発点として使用すると、別の結果セットが得られます:

の変更追跡によるクエリ結果の詳細これは、バージョン476以降のすべての変更のメタデータで、上記のUPDATEステートメントから更新された2つの行のみが含 この変更は挿入ではなく更新であったため、作成バージョンがnullであることに注意してください。 また、SYS_CHANGE_COLUMNS値は現在設定されていますが、値は実際には何が変更されたのかを示していません（まだ）。 これは、変更追跡関数CHANGE_TRACKING_IS_COLUMN_IN_MASK()について話すのに良い時間です。 この関数は、指定された列が最新バージョン以降に更新されているかどうかを確認します。 その構文は少し風変わりですが、MiddleNameが更新されたかどうかを確認するには、クエリは次のようになります。

正直なところ、CHANGE_TRACKING_IS_COLUMN_IN_MASK関数を使用したこと チェックしたい列ごとにこれを実行する必要があるため、少し痛みがあります。 私の仕事のほとんどはデータウェアハウスであり、どの列が更新されたかを正確に知る必要があるケースはほとんどありません–行が更新されたかど しかし、他のシナリオ（特にOLTP）では、これが必要であることがわかります。

私は挿入と更新を実証しました。 削除がどのように見えるかを見てみましょう。 繰り返しますが、次の操作のために、現在のバージョン番号–478–をメモします。 私は今、データの一つの行を削除します:

1つの行を削除したら、CHANGETABLE()を再度実行して、この操作の変更追跡レポートを確認します。

最後の操作で削除した行を見つけ、SYS_CHANGE_OPERATIONをD(delete)に設定します):

今、バージョン番号がここで違いを生むことを覚えておいてください！ CHANGETABLE()に渡されるバージョン番号は、その関数によって返される変更の開始点です。 この演習では、各DML操作の後に変更追跡の結果を確認しています。 ただし、開始バージョン番号を有効なバージョン番号に設定するか、単にNULLを使用してそのテーブルで利用可能なすべての変更追跡結果を取得できます。 実証するために、私はバージョン470に戻って値を設定します–すべての更新の前の出発点–完全な履歴がどのように見えるかを示すために。 元の変更追跡バージョンを使用してCHANGETABLE（）を再実行すると、次のようになります:

ここには予測可能なニュアンスがいくつかあります。 まず第一に、レコードID1を示す行（これは私が削除したPhoebe Buffayレコードでした）は、この行が挿入され、開始バージョン番号以降に削除されたにもかかわらず、単純に削除操作として表示されます。 その行に対する各操作は、変更の追跡には保持されません。 挿入後に両方のレコードを更新したにもかかわらず、SYS_CHANGE_OPERATIONには挿入が表示されますが、挿入後に2行目と4行目に番号が付けられたIdの場合は、SYS_CHANGE_OPERATION これらの行のSYS_CHANGE_VERSIONとSYS_CHANGE_CREATION_VERSIONが一致しないことは、最新の変更が挿入ではなかったことを示しています。

結論

変更の追跡は、SQL Serverでの変更検出のシンプルで軽量な手段です。 変更の追跡を使用すると、新規、変更、および削除されたデータを簡単に識別できるため、ブルートフォース比較が不要になります。 次の投稿では、ETLの観点からこれを見て、変更追跡をエンドツーエンドのロードプロセスに統合します。