SQL Server 2008
データ変更の追跡で遊んでみました♪
データ変更の追跡には2種類存在しています。
この2種類のは履歴データを保持しているか否かです。
それぞれ設定の仕方が異なりますので、2種類書いてみたいと思います。
まずは、変更の追跡からです。
TESTというデータベースを作成して、 tbというテーブルを作成しました。
CREATE TABLE [dbo].[tb](
[id] [decimal](18, 0) NOT NULL,
[value] [nvarchar](max) NULL,
CONSTRAINT [PK_tb] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
この状態で、まずはデータベースに対して「変更の追跡」をするように設定します。
ALTER DATABASE TEST
SET CHANGE_TRACKING = ON
次にテーブルに対して「変更の追跡」をするように設定します。
ALTER TABLE tb
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
この状態でTESTデータベースのtbテーブルに対して「変更の追跡」が実行されています。
以下のSQL文にて「変更の追跡」が実行されていることを確認します。
SELECT *, c.sys_change_version from tb
CROSS APPLY CHANGETABLE ( version tb, (id),(tb.id)) c;
このSQLでエラーメッセージが表示されず、レコードが0件で結果が取得されていればOKです。
ここで「変更の追跡」を行っているtbテーブルと
CROSS APPLYしている
CHANGETABLEは
テーブルに対する変更情報を返す、
変更追跡関数です。
#CHANGETABLE (VERSION)の方を使用しています。さて、ここでテーブルに列を追加し、データを1件追加たいと思います。
実行するSQLは以下のものです。
ALTER TABLE tb ADD [buf] nvarchar(max) null ;
go
insert into tb values(1,N'テスト',CAST(CHANGE_TRACKING_CURRENT_VERSION() as nvarchar));
CHANGE_TRACKING_CURRENT_VERSION()でbuf列に更新前時点でのバージョンを入れておきました。
ここで以下のSQLを実行してみたいと思います。
DECLARE @min_valid_version bigint;
declare @i bigint;
set @min_valid_version =
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('tb'));
set @i = CHANGE_TRACKING_CURRENT_VERSION() -1;
while @i >= @min_valid_version
begin
SELECT
SYS_CHANGE_VERSION,
SYS_CHANGE_CREATION_VERSION,
case SYS_CHANGE_OPERATION
WHEN 'U' THEN 'UPDATE'
WHEN 'I' THEN 'INSERT'
WHEN 'D' THEN 'DELETE'
ELSE '-'
END AS OPERATION_TYPE,
CASE
WHEN
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('tb'),'id','ColumnId'),
SYS_CHANGE_COLUMNS) = 1 THEN 'id'
WHEN
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('tb'),'value','ColumnId'),
SYS_CHANGE_COLUMNS) = 1 THEN 'value'
WHEN
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('tb'),'buf','ColumnId'),
SYS_CHANGE_COLUMNS) = 1 THEN 'buf'
end as TARGET_COLUMN_NAME
from CHANGETABLE
(changes tb, @i) AS c;
set @i = @i -1 ;
end
go
#CHANGETABLE (CHANGES)の方を使用しています。
CHANGETABLE (CHANGES)
SYS_CHANGE_VERSION |
SYS_CHANGE_CREATION_VERSION |
OPERATION_TYPE |
TARGET_COLUMN_NAME |
27 |
27 |
INSERT |
id |
ALTER TABLE tb ADD [buf] nvarchar(max) null ;
go
insert into tb values(1,N'テスト',CAST(CHANGE_TRACKING_CURRENT_VERSION() as nvarchar));
CHANGETABLE (VERSION)
id |
value |
buf |
SYS_CHANGE_VERSION |
SYS_CHANGE_CONTEXT |
id |
sys_change_version |
1 |
テスト |
NULL |
27 |
NULL |
1 |
27 |
次にUPDATEを実行します。
update tb set value = CAST(CHANGE_TRACKING_CURRENT_VERSION() as nvarchar) where id = 1;
そして先ほどと同じSQL(2種)を再実行してみます。
CHANGETABLE (CHANGES)
SYS_CHANGE_VERSION |
SYS_CHANGE_CREATION_VERSION |
OPERATION_TYPE |
TARGET_COLUMN_NAME |
28 |
NULL |
UPDATE |
value |
CHANGETABLE (CHANGES)
SYS_CHANGE_VERSION |
SYS_CHANGE_CREATION_VERSION |
OPERATION_TYPE |
TARGET_COLUMN_NAME |
28 |
27 |
INSERT |
id |
CHANGETABLE (VERSION)
id |
value |
buf |
SYS_CHANGE_VERSION |
SYS_CHANGE_CONTEXT |
id |
sys_change_version |
1 |
27 |
NULL |
28 |
NULL |
1 |
28 |
こんな感じになります。
使い方としては、今までテーブルに更新前日付を持っていて、更新前日付と更新直前に取得した更新前日付が一致したらUPDATEを実行する~
って業務ロジックがあったかと思いますが、これからはこの機能を使用して、更新前のバージョンを取得してチェックする方向になるのかなぁ?って感じがします。
さて、「変更の追跡」の解除をします。
SQLは以下のとおりです。設定はDB→テーブルだったので、解除は逆のテーブル→DBとなります。
ALTER TABLE tb
DISABLE CHANGE_TRACKING
go
ALTER DATABASE TEST
SET CHANGE_TRACKING = OFF
go
さて次に「変更データキャプチャ」を実行してみます。
まずは有効化から行います。
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema='dbo',@source_name='tb',@role_name='cdc_Admin';
1行目が変更データキャプチャの有効化です。
2行目にテーブルに対して変更データキャプチャの設定をしています。
2行目の処理がうまく実行されると以下のようなメッセージが表示されます。
ジョブ 'cdc.TEST_capture' が正常に開始しました。
ジョブ 'cdc.TEST_cleanup' が正常に開始しました。
変更データキャプチャはジョブで動いているようです。
変更データキャプチャも
変更データキャプチャの関数として関数が用意されています。
有効化したデータキャプチャを確認するには以下のSQLを実行してみます。
EXEC sys.sp_cdc_help_change_data_capture @source_schema='dbo',@source_name='tb'
source_schema |
source_table |
capture_instance |
object_id |
source_object_id start_lsn |
end_lsn |
supports_net_changes has_drop_pending role_name |
index_name |
filegroup_name |
create_date |
index_column_list |
captured_column_list |
dbo |
tb |
dbo_tb |
341576255 |
2105058535 |
0x0000001A000000AF0039 |
NULL |
1 |
NULL |
cdc_Admin |
PK_tb |
NULL |
2008-06-30 15:05:54.997 |
[id] |
[id], [value] |
変更データキャプチャ用の関数はキャプチャインスタンスの名前が関数名に入るので、この関数名をチェックしておいてください。
それではこの状態で先ほどと同じように列の追加とINSERT文を実行します。
ALTER TABLE tb ADD [buf] nvarchar(max) null ;
go
insert into tb values(1,N'テスト',CAST(CHANGE_TRACKING_CURRENT_VERSION() as nvarchar));
その後以下のSQLを実行し履歴取得をします。
SELECT
__$start_lsn as CMTLSN,
case __$operation
when 1 then 'DELETE'
when 2 then 'INSERT'
when 3 then 'UPDATE(PREV)'
when 4 then 'UPDATE(AFTER)'
else '-'
end as operation,
id, value
FROM cdc.fn_cdc_get_all_changes_dbo_tb(
(select MIN(__$start_lsn) from cdc.dbo_tb_CT), (select MAX(__$start_lsn) from cdc.dbo_tb_CT), 'all')
order by CMTLSN;
CMTLSN |
operation |
id |
value |
0x0000001E000001BD0013 |
INSERT |
1 |
テスト |
次にUPDATE文です。
update tb set value = CAST(CHANGE_TRACKING_CURRENT_VERSION() as nvarchar) where id = 1;
実行した後に再び履歴取得をしてみます。
CMTLSN |
operation |
id |
value |
0x0000001E000001BD0013 |
INSERT |
1 |
テスト |
0x0000001F0000007D0004 |
UPDATE(AFTER) |
1 |
NULL |
データ変更キャプチャでは変更した分だけデータが蓄積されて、変更内容も保持されているのがわかるかと思います。
ただ、変更データキャプチャを実行した後に追加した列はキャプチャ対象になっていないので注意です。 この用途は今まで業務ロジックで行ってきた履歴管理を丸投げできるって感じですかねぇ…。
・・・っとキャプチャの止め方を記述することを忘れていました。
EXEC sys.sp_cdc_disable_table @source_schema='dbo',@source_name='tb',@capture_instance='dbo_tb';
EXEC sys.sp_cdc_disable_db;
この状態で
EXEC sys.sp_cdc_help_change_data_capture @source_schema='dbo',@source_name='tb';
メッセージ 22901、レベル 16、状態 1、プロシージャ sp_cdc_help_change_data_capture、行 19
データベース 'TEST' で Change Data Capture が有効になっていません。適切なデータベース コンテキストが設定されていることを確認し、操作を再試行してください。Change Data Capture で有効なデータベースについてレポートを作成するには、sys.databases カタログ ビューの is_cdc_enabled 列を照会してください。
となっていれば停止しています。
この後に再度、開始しても今までの履歴はなくなっていますので注意が必要です。
(これはどちらの変更の追跡でも一緒です)
こんな感じで、ちょっと便利なものができたと思っていただけたら幸いです。