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 TESTCT set Change_tracking=on (change_retention=2 days, Auto_cleanup=on)

2. Enable CT for objects to be traced

ALTER TABLE sometable enable change_tracking go

3. Use the changetable function to view the CT data . Sys_change_version is the latest version number of the change for any row in the library.

Returns the most recent change data for a row:

SELECT t.*, ct.* from dbo. SomeTable T Cross APPLY changetable (VERSION dbo. SomeTable, (ID), (t. id)) as CT;

Returns the data changed after the specified last_sync_versiion:

DECLARE @version BIGINT = 2; SELECT t.*, ct.* from changetable (changes dbo. SomeTable, @version) as CT INNER JOIN dbo. SomeTable T on t. id = Ct. ID Go

4. Enable CT for columns

CREATE TABLE dbo.       NewTable (ID INT NOT null IDENTITY (1, 1) CONSTRAINT pk_newtable PRIMARY KEY, Bigcolumn VARCHAR (255) is not NULL , Anotherbigcolumn VARCHAR (255) not NULL) GO ALTER TABLE newtable 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 version SELECT  @version =  change_tracking_current_version () -1; select *, change_tracking_is_column_in_mask (                columnproperty (  OBJECT_ID (' dbo. NewTable '), ' bigcolumn ', ' COLUMNID '                   ),           ct. sys_change_columns               )  as bigcolumn_changed, change_tracking_is_column_in_mask ( columnproperty ( OBJECT_ID (' Dbo. NewTable '),  ' anotherbigcolumn ', ' COLUMNID '                  ),           ct. sys_change_columns               )  as anotherbigcolumn_changed  from changetable (changes dbo. NewTable, @version)  as ct inner join dbo. Newtable nt on ct.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.

This article is from "Joe TJ" blog, be sure to keep this source http://joetang.blog.51cto.com/2296191/1617150

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