変更の追跡機能を使うと、どの行が挿入、更新、削除されたかを追うことができます。
このとき、オプションを付ければ「どのカラムが更新されたか」まで追うことができるので、実験してみました。
※バージョンは2019 developer editionです。
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だけが行の更新が追跡される設定になっていることが分かります。
次に、データをINSERTして、変更の追跡用テーブルをSELECTしてみます。
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
そちらも「I」＝INSERTの履歴が格納されていることが分かります。
また、sys.dm_tran_commit_tableとJOINすることで、コミットした日時も取得することができます。
次に、データをDELETEしてみます。
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
ここまでは、結果は同じです。
最後に、データをUPDATEしてみます。
update t1 set c2 = 6 where c1 = 2
update t2 set c2 = 6 where c1 = 2
どのカラムが更新されたかは、CHANGE_TRACKING_IS_COLUMN_IN_MASK関数を使用します。
パラメータは
CHANGE_TRACKING_IS_COLUMN_IN_MASK ( column_id , change_columns )
となっています。（ドキュメントより引用）
column_idはカラムのID、change_columnsは、changetableデータのsys_change_columns列を使います。
これらを考慮したクエリは以下の通りです。
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
t1の方は、c2だけをUPDATEしているので、is_updateカラムがc2=1となっており、カラムの変更が追跡できていることが分かります。
t2の方は、is_updateカラムがすべて0になる想定だったのですが、何故かすべて1になっています。
原因調査のために試しに以下のクエリを実行したところ、1が返ってきました。
select change_tracking_is_column_in_mask(1, null)
これは想定外の挙動でした。t2では常にsys_change_columnsカラムはNULLになるため、正しい判定ができません。
この挙動を踏まえてクエリを以下のように書き換えてみました。
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
今度は想定した結果が得られました。
まとめ
変更の追跡を使って、どの列が更新されたかを確認する手順について実験してみました。
select change_tracking_is_column_in_mask(1, null)
というクエリが1を返すという挙動は覚えておくとよさそうです。