はじめに

テスト等でデータ更新した際、追加・更新・削除されたレコード（フィールド）を
機械的に拾う方法を探していて、おもしろそうな機能があったので紹介します。

なお、紹介する機能はSQL Server (2008 以降)であれば大丈夫そうですが、
自環境はSQLServer2016 Developerです。

対象

開発者？(MSのサイトをみると利用用途は違います)

参考サイト

データ変更の追跡 (SQL Server)

機能紹介

今回は「Change Data Capture（変更データキャプチャ）」という機能を利用しました。

詳細は参考サイトを確認いただきたいのですが、
簡単な概要と実際の動作を記載します。

「Change Data Capture（変更データキャプチャ）」とは

データベース内のユーザーテーブルに対して行われた DML の変更 (挿入操作、更新操作、および削除操作) を特定する機能です。
当機能を有効化すると、トリガーなどの設定なしでデータやスキーマの変更を追跡できます。

設定の有効化

データベースとユーザテーブルに対してCDC機能の有効化が必要となります。
※上記のMSのサイトを参考に記載します。

データベースの有効化

sys.sp_cdc_enable_db.sql

USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_cdc_enable_db;  
GO

テーブルの有効化（いくつかパラメータがありますが、以下が必須パラメータです。）

sys.sp_cdc_enable_table.sql

USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_cdc_enable_table  
    @source_schema = N'HumanResources'  
  , @source_name = N'Employee'  
  , @role_name = N'cdc_Admin';  
GO

source_schema：dboであることのほうが多いでしょうか。
source_name：キャプチャ対象のテーブルを指定します。

有効化後の状態

システムテーブル内にcdcスキーマのテーブルが作成されます。

テーブル名	内容
cdc.caputerd_columns	・CDCを有効化したテーブルのカラム情報を管理しています。・計算列はvarchar型に自動変換されます。 ※is_computedにフラグがつく。
cdc.change_tables	監視しているテーブルの一覧です。
cdc.dbo_Employee_CT	監視テーブルの中身です。sys.sp_cdc_enable_tableでCDCを有効化したテーブル単位で作成されます。（命名規則）cdc.{実テーブル名}_CT

今回自分が使用したのが上記のみのため、他は割愛させていただきます。

実際の動作（Insert）

サーバのDBに複数人で同時開発することの方が多いかと思いますが、
説明の都合上、今回は該当テーブルには自分しか更新していないと仮定します。

例えば、CDCを有効化しているEmployeeというテーブルに以下のクエリを発行したとします。

insert.sql

insert into Employee(id,name)
values (1,'test1'),(2,'test2'),(3,'test3')

そうすると、「cdc.{実テーブル名}_CT」に以下のようなレコードが挿入されます。

フィールド名	内容
start_lsn	同一トランザクションの場合は同じ値が設定されます。
seqval	主キーの値が同じ場合、同一値が設定されます。今回の場合、主キーが被っていないので全て別の値となります。
operation	DML操作が番号で設定されます。 1：削除、２：新規、3：修正前、4：修正後今回の場合、insertなので、全て２となります。
update_mask	update処理で、どのフィールドが更新されたのかを管理しています。

実際の動作（Update）

update.sql

update Employee set [name] = 'upd_test2' where id = 2

最後の2行が今回更新したレコードです。

「operation = 3：修正前」、「operation = 4：修正後」となり、
修正前後がログとして追加されます。
※「start_lsn」と「seqval」が同一値というところが一つのポイントです。

また、「update_mask」が「0x02」となっていますが、
sys.fn_cdc_is_bit_setで分解すると、どのフィールドが更新されたのかが分かるようになっています。

sys.fn_cdc_is_bit_set.sql

DECLARE @id_ordinal int  = sys.fn_cdc_get_column_ordinal('dbo_Employee','id');
DECLARE @name_ordinal int  = sys.fn_cdc_get_column_ordinal('dbo_Employee','name');

SELECT @id_ordinal idのordinal値, @name_ordinal nameのordinal値

SELECT TOP (1000) [__$start_lsn]
    ,[__$end_lsn]
    ,[__$seqval]
    ,[__$operation]
    ,[__$update_mask]
    ,sys.fn_cdc_is_bit_set(@id_ordinal,[__$update_mask]) as idフィールドが更新
    ,sys.fn_cdc_is_bit_set(@name_ordinal,[__$update_mask]) as nameフィールドが更新
    ,[id]
    ,[name]
FROM [AdventureWorks2012].[cdc].[dbo_Employee_CT]

ここまでのルールが分かると、
ツールに落とし込むのもそんなに難しくありません。

最後に

当然ながらオーバーヘッドは発生しますが、
MSのサイトには以下のような記載もありました。

類似機能のトリガーよりもオーバーヘッドは少なくなる傾向にあるようです。

DML 操作のオーバーヘッドが低減します。同期変更追跡では、オーバーヘッドが常に若干発生します。しかし、変更の追跡を使用すると、オーバーヘッドを最小限に抑えることができます。多くの場合、オーバーヘッドは、代わりのソリューション (特にトリガーを使用する必要があるソリューション) を使用する場合よりも少なくなります。

自分が今回記載したパタン（開発時に利用）はレアパタンだと思いますが、
何かしらの作業のヒントになれば幸いです。