SQL Server 2008 以降では、「変更の追跡」の機能が使用できるようになりました。
データ変更の履歴を確認する機能としては、
- 変更データキャプチャ (Change Data Capture : CDC)
- 変更の追跡
を使用することができ、後者の変更の追跡については SQL Database の v12 Preview でも使用できるようになります。
今回の投稿では変更の追跡の機能について軽くまとめてみたいと思います。
変更の追跡については以下の情報を確認するとよいかと。
データ変更の追跡 (SQL Server)
変更の追跡について (SQL Server)
変更の追跡カタログ ビュー (Transact-SQL)
変更追跡関数 (Transact-SQL)
変更追跡関数を使用した変更の取得
SQL Server 2008 Column Change Tracking
CDC と変更の追跡の違いですが以下のようになります。
CDC では変更されたデータの履歴を保持しますが、変更の追跡ではどのデータが変更されたかという情報のみを保持しており、どのようなデータに変更されたかについては保持されていません。
「どのデータが変更されたか」については判断をすることができるのですが、データを判断するためには主キーが必須となります。
変更の追跡については
- データベースで有効にする
- テーブルで有効にする
の 2 つの手順を実施する必要があります。
今回は SQL Database v12 を使用していますが、同様のクエリはオンプレミスの SQL Server でも実行することができます。
Contents
■データベースで機能を有効化
1. の作業として以下のクエリを実行します。
CREATE DATABASE [ChangeTracking] COLLATE Japanese_XJIS_100_CI_AS_SC (EDITION='basic', SERVICE_OBJECTIVE = 'basic') GO ALTER DATABASE [ChangeTracking] SET CHANGE_TRACKING = ON ALTER DATABASE [ChangeTracking] SET CHANGE_TRACKING(CHANGE_RETENTION = 5 MINUTES, AUTO_CLEANUP = ON) GO
DB を作成し、その後、変更の追跡を有効にしています。
変更の追跡はデータが蓄積されていきますので、保持期間を 5 分とし、変更履歴を自動的に削除するように設定しています。
これでデータベースの設定は完了です。
■テーブルで機能を有効化
次に変更を追跡するテーブルを作成します。
今回は以下のようなテーブルを作成しています。
CREATE TABLE [dbo].[SourceTable]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [uniqueidentifier] NOT NULL, [Col3] [uniqueidentifier] NULL, [Col4] [uniqueidentifier] NULL CONSTRAINT [PK_SourceTable] PRIMARY KEY CLUSTERED ([Col1], [Col2] ASC)) GO ALTER TABLE SourceTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) GO
今回のテーブルでは列の変更についても追跡をするようにしています。
これで準備は完了です。
まずは、以下のようなクエリを実行してみます。
INSERT INTO SourceTable(Col2,Col3) VALUES(NEWID(), NEWID()), (NEWID(), NEWID()), (NEWID(), NEWID()) INSERT INTO SourceTable(Col2,Col3) VALUES(NEWID(), NEWID()), (NEWID(), NEWID()), (NEWID(), NEWID())
このクエリを実行することで、6 件のデータが INSERT されます。
変更の追跡では主に、CHANGETABLE テーブル関数を利用します。
この関数では、VERSION と CHANGES を使用することができ、各データのバージョンと変更の情報を取得することができます。
まずは VERSION を使用して全データのバージョンを取得してみたいと思います。
SELECT * FROM SourceTable AS s CROSS APPLY CHANGETABLE (VERSION SourceTable, (Col1, Col2), (s.Col1, s.Col2)) AS c
実行結果が以下になります。
最初の INSERT が VERSION 1 / 2 回目の INSERT が VERSION 2 として内部的に記録されているのが確認できますね。
それでは次に変更の情報を取得してみたいと思います。
DECLARE @last_synchronization_version bigint = (SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('SourceTable'))) SELECT * FROM SourceTable AS s RIGHT OUTER JOIN CHANGETABLE (CHANGES SourceTable, @last_synchronization_version) AS c ON c.Col1 = s.Col1 ORDER BY s.col1
このようなクエリを実行することでそのデータでどのような変更が行われたのかを確認することができます。
# CHANGES では指定したバージョンより大きい情報を取得することができます。
変更の確認については以下のようなクエリを記載することも可能です。
DECLARE @synchronization_version bigint = CHANGE_TRACKING_CURRENT_VERSION() DECLARE @context varbinary(128) = CONVERT(varbinary, 'Test Context'); WITH CHANGE_TRACKING_CONTEXT (@context) UPDATE SourceTable SET Col3 = NEWID() WHERE Col1 = 1 SELECT *, CHANGE_TRACKING_IS_COLUMN_IN_MASK((COLUMNPROPERTY(OBJECT_ID('SourceTable'), 'Col3', 'ColumnId')), c.SYS_CHANGE_COLUMNS) AS Col3_Changed FROM SourceTable AS s RIGHT OUTER JOIN CHANGETABLE (CHANGES SourceTable, @synchronization_version) AS c ON c.Col1 = s.Col1 ORDER BY s.col1
CHANGE_TRACKING_IS_COLUMN_IN_MASK で特定の列が変更されたのかを確認することが可能です。
また、CHANAGE_TRACKING_CONTEXT により、変更時の追跡をしやすいようにコンテキストを指定することもできます。
変更の追跡については内部テーブルに格納されており、以下のクエリで存在を確認することができます。
SELECT * FROM sys.internal_tables WHERE name LIKE 'change%'
SQL Database については専用管理者接続で接続ができないため内部テーブルにアクセスができませんが、オンプレミスの SQL Server であれば、以下のように内部テーブルから情報を取得することが可能です。
変更されたデータを取得することはできますが利用方法についてはいろいろと考慮する必要がある機能ですね…。
変更データはSSISでやっつけると新しい世界が広がって楽しいです。
Yoshihiro Matsumoto
22 1月 15 at 23:40