【SQLServer】追加・更新・削除されたレコード（フィールド）を機械的に拾う方法

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

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

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


# 参考サイト

[データ変更の追跡 (SQL Server)](https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-2017)


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

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

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

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

- データベースの有効化

```sql:sys.sp_cdc_enable_db.sql
USE AdventureWorks2012;  
GO  
EXECUTE sys.sp_cdc_enable_db;  
GO
```
- テーブルの有効化（いくつかパラメータがありますが、以下が必須パラメータです。）

```sql: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スキーマのテーブルが作成されます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/2de67b82-4870-1747-3c04-f3600e3c74eb.png)

| テーブル名       | 内容|
|:-----------------|:-----------------|
| cdc.caputerd_columns             | ・CDCを有効化したテーブルのカラム情報を管理しています。<br>・計算列はvarchar型に自動変換されます。<br>※is_computedにフラグがつく。<br> ![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/8e582787-4f88-be58-11d9-2a85b3a2f5fe.png)|
| cdc.change_tables           | 監視しているテーブルの一覧です。<br>![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/4503b10c-69ea-9268-3c44-00f6fcff84cd.png) |
| cdc.dbo_Employee_CT             | 監視テーブルの中身です。sys.sp_cdc_enable_tableでCDCを有効化したテーブル単位で作成されます。<br><br>（命名規則）cdc.{実テーブル名}_CT<br>![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/b13fea5e-f3b7-87ef-d9c3-e4994f486c13.png) |

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

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

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

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

そうすると、「cdc.{実テーブル名}_CT」に以下のようなレコードが挿入されます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/3d7389dd-d9c2-8ec2-6841-f10e29278c45.png)

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

## 実際の動作（Update）

```sql:update.sql
update Employee set [name] = 'upd_test2' where id = 2
```

最後の2行が今回更新したレコードです。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/bab94739-7f0a-48fd-4a82-0cdb48318f28.png)

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

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


```sql: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]
```

![image.png](https://qiita-image-store.s3.amazonaws.com/0/124594/a4b6c521-976f-1d0a-1ac1-58e0bb1c464d.png)

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

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

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

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


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