SQL Server: 変更の追跡で変更された列を判断する

[変更の追跡](https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15)機能を使うと、どの行が挿入、更新、削除されたかを追うことができます。

このとき、オプションを付ければ「どのカラムが更新されたか」まで追うことができるので、実験してみました。
※バージョンは2019 developer editionです。

```sql
create database change_tracking
go
use change_tracking

-- DBレベルで変更の追跡を有効化
alter database [change_tracking] set change_tracking = on
go

--テーブル作成
create table t1 (pk int identity(1,1) not null primary key, c1 int not null, c2 int not null)
create table t2 (pk int identity(1,1) not null primary key, c1 int not null, c2 int not null)

--変更の追跡の有効化(行の更新の追跡：ON）
alter table t1 enable change_tracking
with (track_columns_updated = on)
go

--変更の追跡の有効化(行の更新の追跡：OFF）
alter table t2 enable change_tracking
go

--設定を確認
select top 100 object_name(object_id) as table_name, * from sys.change_tracking_tables
```

サンプルクエリです。実行すると、行の更新の追跡をONにしてchange_trackingを有効化したt1と、change_trackingを有効化しただけのt2が作成されます。

システムテーブルのSELECT結果からも、t1だけが行の更新が追跡される設定になっていることが分かります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/0ecd3a4e-a4d3-771c-d564-d5d105f8a217.png)

次に、データをINSERTして、変更の追跡用テーブルをSELECTしてみます。

```sql
insert into t1 (c1, c2) values (1, 1), (2,2), (3,3), (4,4), (5,5)
insert into t2 (c1, c2) values (1, 1), (2,2), (3,3), (4,4), (5,5)
go

select
    *
from
     changetable(changes t1, 0) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
order by tc.commit_ts

select
    *
from
     changetable(changes t2, 0) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
order by tc.commit_ts
go
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/df9e4f76-dbcc-0369-6a57-2a99c8aeecc2.png)
そちらも「I」＝INSERTの履歴が格納されていることが分かります。
また、sys.dm_tran_commit_tableとJOINすることで、コミットした日時も取得することができます。

次に、データをDELETEしてみます。

```sql
delete from t1 where c1 = 1
delete from t2 where c1 = 1

select
    *
from
     changetable(changes t1, 0) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
order by tc.commit_ts

select
    *
from
     changetable(changes t2, 0) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
order by tc.commit_ts
go
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/3bf51662-57b6-54bf-97fc-29b43b75baae.png)

ここまでは、結果は同じです。

最後に、データをUPDATEしてみます。

```sql
update t1 set c2 = 6 where c1 = 2
update t2 set c2 = 6 where c1 = 2
```

どのカラムが更新されたかは、[CHANGE_TRACKING_IS_COLUMN_IN_MASK](https://docs.microsoft.com/ja-jp/sql/relational-databases/system-functions/change-tracking-is-column-in-mask-transact-sql?view=sql-server-ver15)関数を使用します。

パラメータは

```sql
CHANGE_TRACKING_IS_COLUMN_IN_MASK ( column_id , change_columns )
```

となっています。（ドキュメントより引用）
column_idはカラムのID、change_columnsは、changetableデータのsys_change_columns列を使います。
これらを考慮したクエリは以下の通りです。

```sql
select
    change_tracking_is_column_in_mask(column_id , sys_change_columns) as is_updated
    ,*
from
     changetable(changes t1, 3) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
cross join sys.columns where object_id = object_id('t1')
order by tc.commit_ts

select
    change_tracking_is_column_in_mask(column_id , sys_change_columns) as is_updated
    ,*
from
     changetable(changes t2, 4) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
cross join sys.columns where object_id = object_id('t1')
order by tc.commit_ts
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/96a11f20-0d01-f03f-8236-8ce8ae3e4501.png)

t1の方は、c2だけをUPDATEしているので、is_updateカラムがc2=1となっており、カラムの変更が追跡できていることが分かります。
t2の方は、is_updateカラムがすべて0になる想定だったのですが、何故かすべて1になっています。
原因調査のために試しに以下のクエリを実行したところ、1が返ってきました。

```sql
select change_tracking_is_column_in_mask(1, null)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/ad368446-781f-9db7-897c-576121ef1c3f.png)

これは想定外の挙動でした。t2では常にsys_change_columnsカラムはNULLになるため、正しい判定ができません。

この挙動を踏まえてクエリを以下のように書き換えてみました。

```sql
select
    (case when sys_change_columns is null then 0 else change_tracking_is_column_in_mask(column_id , sys_change_columns) end) as is_updated
    ,*
from
     changetable(changes t1, 3) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
cross join sys.columns where object_id = object_id('t1')
order by tc.commit_ts

select
    (case when sys_change_columns is null then 0 else change_tracking_is_column_in_mask(column_id , sys_change_columns) end) as is_updated
    ,*
from
     changetable(changes t2, 4) as c
join sys.dm_tran_commit_table tc on c.sys_change_version = tc.commit_ts
cross join sys.columns where object_id = object_id('t1')
order by tc.commit_ts
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148310/c5875bf5-bcfe-b42f-df10-1f87b06bd59d.png)

今度は想定した結果が得られました。

###まとめ
変更の追跡を使って、どの列が更新されたかを確認する手順について実験してみました。

```sql
select change_tracking_is_column_in_mask(1, null)
```
というクエリが1を返すという挙動は覚えておくとよさそうです。