SQL Server 2008:CDC and change tracking

Source: Internet
Author: User
Tags sql server books new features

There is often a need to record data changes caused by DML operations (Insert/updae/delete) in the user table, and in previous versions of SQL Server 2008, such functionality could only be achieved through trigger or data alignment (for example, SCD processing). and must be developed for each user table. There are two new features for recording data changes in SQL Server 2008, and this article makes a brief comparison of the features of the change data Capture (CDC) and changing tracking.

Change Data Capture

The CDC records data changes in the time, type, and actual impact of a DML operation by asynchronously reading the transaction log, and then logs the data to a table that is automatically created when the CDC is enabled. Detailed data changes can be obtained through CDC-related stored procedures. Because the data change is read asynchronously, it has little effect on the overall performance, much less than the data change record implemented by trigger.

The following figure is from SQL Server Books Online, which explains how the CDC can be used to get changes within different time periods.

Change tracking

Unlike change Data capture,change tracking only records the occurrence time, type, and affected fields of a DML operation, does not contain a specific variable value, and the client obtains the change record from the last sync to the current by passing the last synchronized version number. For applications where data caching is applied, this is a useful feature that allows you to get a record of the changes in the datasheet at any given time, and then refresh the cache with the updated data based on the primary key in the change record.

The change tracking gets the variance record by analyzing the DML statement to be executed, rather than reading the log. The change tracking response is faster than the change Data capture when the DML statement commits execution and the change tracking is available without waiting for the transaction log write to be available after the DML completes.

The change tracking records less data than the capture, and has little impact on server performance.

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.