SqlServer 變更追蹤（Chang Tracking），sqlservertracking

SqlServer 變更追蹤（Chang Tracking），sqlservertracking

對於追蹤資料庫表的 DML 操作，SQLserver 2008 及以上版本提供了 變更資料擷取和變更追蹤。

變更資料庫捕獲 與 跟蹤更改 的區別：

變更資料擷取與變更追蹤都是記錄表的DML操作

變更資料擷取可把操作資料的曆史值儲存下來；變更追蹤捕獲更改了表行這一事實，但不會捕獲更改的資料。

變更資料擷取使用非同步進程捕獲，該進程掃描交易記錄；變更追蹤同步跟蹤DML操作

變更資料擷取儲存在當前資料庫system表中，更改表可指定儲存位置；變更追蹤表格儲存體在系統架構sys中，不可查看結構定義

更多參考：比較變更資料擷取和變更追蹤

【接下來測試】

建立測試表：

USE [MyDatabase]GOCREATE TABLE [dbo].[TestTab]([id] [int] NOT NULL,[name] [varchar](50) NOT NULL,[insertDate] [datetime] NOT NULL,[value] [numeric](14, 4) NULL,[info] [varchar](20) NULL,CONSTRAINT [PK_TestTab] PRIMARY KEY CLUSTERED ([id] ASC)ON [PRIMARY]) ON [PRIMARY]GO

啟用資料庫變更追蹤：（參考：ALTER DATABASE SET 選項 (Transact-SQL)）

USE [master]GOALTER DATABASE [MyDatabase] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)GO

啟用表的變更追蹤：（參考：ALTER TABLE (Transact-SQL)）

USE [MyDatabase]GOALTER TABLE [dbo].[TestTab] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)GO

查看資料庫或表啟用情況：（參考：sys.change_tracking_databases，sys.change_tracking_tables）

SELECT DB_NAME(database_id) DBName,retention_period,retention_period_units_desc,is_auto_cleanup_onFROM sys.change_tracking_databases SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_onFROM sys.change_tracking_tables

那跟蹤表的資料存放區在哪呢？使用內部表可查看

-- 每個啟用跟蹤的表儲存一行SELECT * FROM sys.internal_tables WHERE internal_type_desc='CHANGE_TRACKING'SELECT * FROM sys.sysobjects WHERE id = (SELECT object_id FROM sys.internal_tables WHERE internal_type_desc='CHANGE_TRACKING'AND parent_object_id=object_id('TestTab'))--查看錶大小exec sp_spaceused 'sys.change_tracking_37575172'exec sp_spaceused 'sys.syscommittab'

插入測試資料：

--插入測試資料insert into [dbo].[TestTab](id,name,insertDate,value,info)select 1,'kk',GETDATE(),10,'info'union allselect 2,'GG',GETDATE(),50,''union allselect 3,'MM',GETDATE(),0,null

怎麼查看跟蹤資訊，系統提供了一個表值函數 CHANGETABLE ：

--返回<版本號碼>之後的所有行更改資訊（如：查看版本號碼為0及之後的所有更改資訊）--SELECT * FROM CHANGETABLE(CHANGES <表名>,<版本號碼>) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as T--返回指定行的最新變更追蹤資訊（如： id=3 最新更改情況）--SELECT * FROM CHANGETABLE(VERSION <表名>,(<主鍵列>),(<主索引值>)) as TSELECT * FROM CHANGETABLE(VERSION dbo.TestTab,(id),(3)) as T

CHANGETABLE CHANGES:

列名	資料類型	說明
SYS_CHANGE_VERSION	bigint	與上次對行的更改關聯的版本值。（同一批次操作，版本號碼相同）
SYS_CHANGE_CREATION_VERSION	bigint	與上次插入操作關聯的版本值。（同一批次操作，版本號碼相同）
SYS_CHANGE_OPERATION	nchar(1)	指定更改的類型： U = 更新 I = 插入 D = 刪除
SYS_CHANGE_COLUMNS	varbinary(4100)	列出自基準版本以後發生了更改的列。  [注意] 計算資料行永遠不會作為更改的列列出。 以下任何一個條件為真時，值為 NULL： *未啟用列變更追蹤。 *操作是插入操作或刪除操作。 *在一個操作中更新了所有非主鍵列。不應直接解釋此二進位值。
SYS_CHANGE_CONTEXT	varbinary(128)	更改可以在 INSERT、UPDATE 或 DELETE 語句中使用 WITH 子句選擇指定的上下文資訊。
<<主鍵列值>>	與使用者表列相同	被跟蹤表的主鍵值。這些值在使用者表中唯一標識各行。（與當前表串連反應最新資料）

CHANGETABLE VERSION :

列名	資料類型	說明
SYS_CHANGE_VERSION	bigint	與行關聯的當前更改版本值。 如果在超過變更追蹤保留期的時段內沒有變更，或者在啟用變更追蹤之後未更改行，則值為 NULL。
SYS_CHANGE_CONTEXT	varbinary(128)	更改可以在 INSERT、UPDATE 或 DELETE 語句中使用 WITH 子句選擇指定的上下文資訊。
<<主鍵列值>>	與使用者表列相同	被跟蹤表的主鍵值。這些值在使用者表中唯一標識各行。（與當前表串連反應最新資料）

更改資料：

update [dbo].[TestTab] set insertDate = GETDATE(),info = 'update' where id=3update [dbo].[TestTab] set insertDate = GETDATE(),value = 100 where id=3 --執行了2次

檢查版本號碼資訊：（參考：CHANGE_TRACKING_MIN_VALID_VERSION，CHANGE_TRACKING_CURRENT_VERSION）

--擷取一個表的最低有效版本號碼SELECT CHANGE_TRACKING_MIN_VALID_VERSION( OBJECT_ID('dbo.TestTab'))--返回與上次提交的事務相關聯的版本,以確定下次需要更改時將使用的版本SELECT CHANGE_TRACKING_CURRENT_VERSION()

--------------------0(1 行受影響)--------------------4(1 行受影響)

再次用CHANGETABLE 查看：

SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as T

為什麼不是更新（SYS_CHANGE_OPERATION = 'U'）？？

last_sync_version = 0 為當時的跟蹤情況，現在 SYS_CHANGE_VERSION 已經大於1 了，要查詢各狀態當時更改情況，如：

--返回最後一次版本號碼之後的更改(需指定 last_sync_version )--該值 last_sync_version 必須保持記住,下次可以現在查到的@last_sync_version = CHANGE_TRACKING_CURRENT_VERSION()SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,0) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,1) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,2) as TSELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,3) as T

註：最後兩次更改是相同的的，所以 SYS_CHANGE_COLUMNS 一樣。

還可以使用 CHANGE_TRACKING_IS_COLUMN_IN_MASK查看更改列情況 (需啟用 TRACK_COLUMNS_UPDATED = ON )

--檢查變更追蹤(查看哪些列曾更改)SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK (<column_id>,<change_columns>)--column_id : 是正在被檢查的列的 ID。 可以使用 COLUMNPROPERTY 函數擷取此列 ID。--change_columns : 是 CHANGETABLE 資料的 SYS_CHANGE_COLUMNS 列中的位元據。--(如主鍵列更改,視為刪除舊值,插入新值)SELECT id,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'id','COLUMNID'),SYS_CHANGE_COLUMNS) id_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'name','COLUMNID'),SYS_CHANGE_COLUMNS) name_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'insertDate','COLUMNID'),SYS_CHANGE_COLUMNS) insertdate_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'value','COLUMNID'),SYS_CHANGE_COLUMNS) value_is_changed,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('TestTab'),'info','COLUMNID'),SYS_CHANGE_COLUMNS) info_is_changedFROM CHANGETABLE(CHANGES dbo.TestTab,1) AS TWHERE SYS_CHANGE_OPERATION='U'

在進行刪除和插入操作：

delete from [dbo].[TestTab] where id=1insert into [dbo].[TestTab](id,name,insertDate,value,info)select 4,'kk',GETDATE(),10,'info'

--查看版本號碼為3之後都操作了什麼?SELECT * FROM CHANGETABLE(CHANGES dbo.TestTab,3) as T

對於當前表的記錄是：

SELECT T.id,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,D.*FROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TLEFT JOIN [dbo].[TestTab] AS D ON T.id = D.id

對於資料定時轉移的情況:源表中資料進行了 insert,update,delete 操作目標表操作如下:insert : 跟蹤表關聯源表,查詢到的資料插入到目標表中.(即使插入後的資料發送更改,在改版本號碼之顯示還是操作插入"I")update : 跟蹤表關聯源表,查詢到的資料更新到目標表中.也可使用函數 CHANGE_TRACKING_IS_COLUMN_IN_MASK 更新了哪些列delete : 對於刪除 (SYS_CHANGE_OPERATION = 'D'),跟蹤表直接對目標表進行刪除(注意主鍵不可相同!否則可能刪錯)

--資料轉移情況,根據版本號碼更改,所以版本號碼要記住!源表: [TestTab]目標表: [TargetTestTab]--insert : INSERT INTO [TargetTestTab](id,name,insertDate,value,info)SELECT D.id,name,insertDate,value,infoFROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TINNER JOIN [dbo].[TestTab] AS D ON T.id = D.idWHERE SYS_CHANGE_OPERATION = 'I'--update : UPDATE K SET K.id=D.id,K.name=D.name,K.insertDate=D.insertDate,K.value=D.value,K.info=D.infoFROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TINNER JOIN [dbo].[TestTab] AS D ON T.id = D.idINNER JOIN [dbo].[TargetTestTab] K ON D.id=K.idWHERE SYS_CHANGE_OPERATION = 'U'--delete : DELETE FROM [dbo].[TargetTestTab] WHERE id IN(SELECT id FROM CHANGETABLE(CHANGES dbo.TestTab,3) AS TWHERE SYS_CHANGE_OPERATION = 'D')

轉移資料前，先查看當前的版本號碼 CHANGE_TRACKING_CURRENT_VERSION()  ，例如500 。而上面的操作中，版本號碼為3，這批資料相應更改完成後，下次從版本號碼500開始。當然可能有誤差，因為在操作這批資料時，當前可能有其他動作，版本號碼也在累計。

建議使用快照隔離等級，保證事務的一致性：

ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;

最後禁用變更追蹤:

--對錶禁用ALTER TABLE [dbo].[TestTab] DISABLE CHANGE_TRACKING GO--對資料庫禁用ALTER DATABASE [MyDatabase] SET CHANGE_TRACKING = OFF GO

參考:

變更追蹤概述

變更追蹤

使用變更追蹤函數擷取更改