SqlServer changtracking and sqlservertracking

SqlServer changtracking and sqlservertracking

For DML operations that track database tables, SQLserver 2008 and later provide Change Data Capture and change tracking.

Differences Between Change Database capture and tracking changes:

Change Data Capture and change tracking are all DML operations on the record table.

Change Data Capture saves the historical values of the operation data. Change tracking captures the fact that the table row is changed, but does not capture the changed data.

Change Data Capture uses asynchronous process capture, which scans transaction logs; change tracking synchronizes DML operations

Change Data Capture is stored in the system table of the current database. You can change the storage location of a table. Change the tracking table is stored in the system architecture sys and cannot view the structure definition.

For more information, see compare Change Data Capture and change tracking.

[Next test]

Create a test table:

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

Enable DATABASE Change tracking: (See alter database set Option (Transact-SQL ))

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

Enable TABLE change tracking: (See alter table (Transact-SQL ))

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

View database or table activation information: (reference: 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

Where is the data stored in the tracking table? Use internal tables to view

-- Save a 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 ') -- view the table size exec sp_spaceused 'sys. change_tracking_37575172 'exec sp_spaceused 'sys. syscommittab'

Insert test data:

-- Insert test data 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

The system provides a table value function CHANGETABLE to view the tracing information:

-- Return the CHANGES to all rows after <version> (for example, view all CHANGES after version 0) -- SELECT * from changetable (CHANGES <Table Name>, <version>) as TSELECT * from changetable (CHANGES dbo. testTab, 0) as T -- return the latest change trace information of the specified row (for example, id = 3 latest changes) -- SELECT * from changetable (VERSION <Table Name>, (<primary key column>), (<primary key value>) as TSELECT * from changetable (VERSION dbo. testTab, (id), (3) as T

Changetable changes:

Column name	Data Type	Description
SYS_CHANGE_VERSION	Bigint	The version value associated with the last change to the row. (The same batch of operations with the same version number)
SYS_CHANGE_CREATION_VERSION	Bigint	The version value associated with the last insert operation. (The same batch of operations with the same version number)
SYS_CHANGE_OPERATION	Nchar (1)	Specify the type of change: U = Update I = insert D = Delete
SYS_CHANGE_COLUMNS	Varbinary (4100)	Lists the columns that have been changed since the baseline version. [Note] The calculated column is never listed as the changed column. If any of the following conditions is true, the value is NULL: * Column change tracking is not enabled. * An operation is an insert or delete operation. * All non-primary key columns are updated in one operation. This binary value should not be directly interpreted.
SYS_CHANGE_CONTEXT	Varbinary (128)	You can use the WITH clause in INSERT, UPDATE, or DELETE statements to select the specified context.
<Primary key column value>	Same as the user table column	Primary Key Value of the tracked table. These values uniquely identify each row in the User table. (Connect to the current table to reflect the latest data)

Changetable version:

Column name	Data Type	Description
SYS_CHANGE_VERSION	Bigint	The version value currently changed associated with the row. The value is NULL if no change is made during the period beyond the change tracking retention period, or the row is not changed after the change tracking is enabled.
SYS_CHANGE_CONTEXT	Varbinary (128)	You can use the WITH clause in INSERT, UPDATE, or DELETE statements to select the specified context.
<Primary key column value>	Same as the user table column	Primary Key Value of the tracked table. These values uniquely identify each row in the User table. (Connect to the current table to reflect the latest data)

Change Data:

Update [dbo]. [TestTab] set insertDate = GETDATE (), info = 'update' where id = 3 update [dbo]. [TestTab] set insertDate = GETDATE (), value = 100 where id = 3 -- twice

Check the version number: (See CHANGE_TRACKING_MIN_VALID_VERSION, CHANGE_TRACKING_CURRENT_VERSION)

-- Obtain the minimum valid version number of a table: SELECT CHANGE_TRACKING_MIN_VALID_VERSION (OBJECT_ID ('dbo. testTab ') -- returns the version associated with the last transaction to determine the version to be used when the next change is required. SELECT CHANGE_TRACKING_CURRENT_VERSION ()

-------------------- 0 (1 line affected) ------------------ 4 (1 line affected)

Check again with CHANGETABLE:

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

Why not update (SYS_CHANGE_OPERATION = 'U ')??

Last_sync_version = 0 indicates the current tracking. Now SYS_CHANGE_VERSION is greater than 1. You need to query the changes of each status at that time, for example:

-- Return the CHANGES after the last version number (last_sync_version must be specified) -- the value of last_sync_version must be kept in mind. Next time, you can find @ last_sync_version = CHANGE_TRACKING_CURRENT_VERSION () SELECT * FROM CHANGETABLE. 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

Note: The last two changes are the same, so SYS_CHANGE_COLUMNS is the same.

You can also use CHANGE_TRACKING_IS_COLUMN_IN_MASK to view the changed columns (TRACK_COLUMNS_UPDATED = ON must be enabled)

-- Check change tracking (to view which columns have changed) SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK (<column_id>, <change_columns>) -- column_id: ID of the column being checked. You can use the COLUMNPROPERTY function to obtain the ID of this column. -- Change_columns: binary data in the SYS_CHANGE_COLUMNS column of CHANGETABLE data. -- (If the primary key column is changed, it is regarded as deleting the old value and inserting the new value) SELECT id, CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY (OBJECT_ID ('testtab '), 'id', 'columnid '), SYS_CHANGE_COLUMNS) id_is_changed, struct (COLUMNPROPERTY (OBJECT_ID ('testtab '), 'name', 'columnid'), SYS_CHANGE_COLUMNS) name_is_changed, struct (COLUMNPROPERTY (OBJECT_ID ('testtab '), 'insertdate', 'columnid'), SYS_CHANGE_COLUMNS) insertdate_is_changed, struct (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'

Perform the following operations on deletion and insertion:

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

-- Check what operations are performed after the version number is 3? SELECT * from changetable (CHANGES dbo. TestTab, 3) as T

The record for the current table is:

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

For timed data transfer: insert, update, and delete operations are performed on the data in the source table. The target table is operated as follows: insert: The tracking table is associated with the source table, and the queried data is inserted into the target table. (even if the inserted data is changed, the "I" operation is still inserted when the version number is changed.) update: The tracking table is associated with the source table and the queried data is updated to the target table. you can also use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function to update which columns delete: For delete (SYS_CHANGE_OPERATION = 'D'), the trail table directly deletes the target table (note that the primary keys cannot be the same! Otherwise, an error may be deleted)

-- The data transfer status is changed according to the version number, so remember the version number! Source table: [TestTab] target table: [TargetTestTab] -- insert: insert into [TargetTestTab] (id, name, insertDate, value, info) select d. id, name, insertDate, value, infoFROM CHANGETABLE (CHANGES dbo. testTab, 3) join as tinner [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) join as tinner [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 ')

Before transferring data, check the current version CHANGE_TRACKING_CURRENT_VERSION (), for example, 500. In the above operation, the version number is 3. After the data is changed, it starts from version 500 next time. Of course, there may be errors, because there may be other operations while operating this batch of data, and the version number is also accumulating.

We recommend that you use the Snapshot isolation level to ensure transaction consistency:

ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;

Finally, disable change tracking:

-- Disable alter table [dbo]. [TestTab] DISABLE CHANGE_TRACKING GO for a TABLE -- disable alter database [MyDatabase] SET CHANGE_TRACKING = off go for a DATABASE

Refer:

Change tracking Overview

Change tracking

Use the change tracking function to obtain changes