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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.