はじめに
テスト等でデータ更新した際、追加・更新・削除されたレコード（フィールド）を
機械的に拾う方法を探していて、おもしろそうな機能があったので紹介します。
なお、紹介する機能はSQL Server (2008 以降)であれば大丈夫そうですが、
自環境はSQLServer2016 Developerです。
対象
- 開発者？(MSのサイトをみると利用用途は違います)
参考サイト
機能紹介
今回は「Change Data Capture（変更データ キャプチャ）」という機能を利用しました。
詳細は参考サイトを確認いただきたいのですが、
簡単な概要と実際の動作を記載します。
「Change Data Capture（変更データ キャプチャ）」とは
データベース内のユーザー テーブルに対して行われた DML の変更 (挿入操作、更新操作、および削除操作) を特定する機能です。
当機能を有効化すると、トリガーなどの設定なしでデータやスキーマの変更を追跡できます。
設定の有効化
データベースとユーザテーブルに対してCDC機能の有効化が必要となります。
※上記のMSのサイトを参考に記載します。
- データベースの有効化
USE AdventureWorks2012;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
- テーブルの有効化（いくつかパラメータがありますが、以下が必須パラメータです。）
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 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 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で分解すると、どのフィールドが更新されたのかが分かるようになっています。
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 操作のオーバーヘッドが低減します。 同期変更追跡では、オーバーヘッドが常に若干発生します。 しかし、変更の追跡を使用すると、オーバーヘッドを最小限に抑えることができます。 多くの場合、オーバーヘッドは、代わりのソリューション (特にトリガーを使用する必要があるソリューション) を使用する場合よりも少なくなります。
自分が今回記載したパタン（開発時に利用）はレアパタンだと思いますが、
何かしらの作業のヒントになれば幸いです。