Getting Started with SQL Server auditing: Change Tracking (Tracking)

Introduction

Change tracking is a lightweight solution that provides an efficient change-tracking mechanism for your application. Regular, custom change tracking and read tracking data, which uses triggers, stored procedures, and user tables that record changes, etc.

There is also a need to ensure consistent access and related cleanup efforts. Using CT will reduce additional effort and guarantee access consistency.

A primary key must be available on the CT-enabled table, and all versions are available. In order to ensure the validity of the change information, the official recommendation is to use snapshot isolation.

CT can only provide:

Which rows/columns have changed

The latest data for the row being changed.

cannot provide : the number of times the change occurred, intermediate value.

application : Offline cache update, ETL update

Precautions:

1. Write changes to the Change log table, so the execution plan will have one more operator, increasing the overhead.

2. Each CT-enabled table creates an internal change table.

3. For each change in each row in the user table, a row is added to the internal change table. The row has a small fixed overhead, plus a variable cost that is equal to the size of the primary key column.

4. For each committed transaction, a row is added to the internal transaction table. Implement

1. Enable CT at the library level

Alter Database  Set change_tracking= on (   change_retention=2 Days ,   auto_cleanup= on )

2. Enable CT for objects to be traced

Alter Table  Go

3. Use changetable function View CT data。 Sys_change_version is any row in the libraryThe latest version number of the change.

Returns the most recent change data for a row:

SELECT T.*       , CT. *  from   Cross as CT;

Returns the specified last_sync_versiion afterChanged data:

DECLARE @version BIGINT = 2  SELECT T.*  , CT. *  from @version  as CT        INNER JOIN  on =  Go

4. Enable CT for columns

CREATE TABLEdbo. NewTable (IDINT  not NULL IDENTITY(1,1)CONSTRAINTPk_newtablePRIMARY KEY, BigcolumnVARCHAR(255) not NULL, AnotherbigcolumnVARCHAR(255) not NULL ) GO ALTER TABLEnewtable ENABLE change_tracking with(track_columns_updated= on)

5. Query the CT column for change data

Use Change_tracking_is_column_in_mask to combine changetable

DECLARE @version BIGINT ; --Get Previous versionSELECT @version=Change_tracking_current_version ()-1; SELECT *, Change_tracking_is_column_in_mask (ColumnProperty(object_id('dbo. NewTable'),'Bigcolumn','COLUMNID'), CT. Sys_change_columns) asbigcolumn_changed, Change_tracking_is_column_in_mask (ColumnProperty(                         object_id('dbo. NewTable'),                         'Anotherbigcolumn','COLUMNID'), CT. Sys_change_columns) asanotherbigcolumn_changed fromChangetable (Changes dbo. NewTable,@version) asCTINNER JOINdbo. NewTable NT onCt.id=Nt.id

Summary

1. The so-called light weight, is relative. The CT still needs to record the change record, which requires additional overhead.

2. CT can only record the final value, so the actual application is not used much, only for the specific scene.

Getting Started with SQL Server auditing: Change Tracking (Tracking)