SQL Server-change data tracking 1

Some applications ( e.g. Data Warehouse system ) relies heavily on the change data recognition capabilities of the source system, which expects the source system to provide an efficient and low ability to identify change data. There was no original ecosystem solution before SQL Server, and developers had to implement it by using triggers, timestamps, and additional tables , and after SQL Server2008 , the database provided two primitive features: change tracking and change data Capture ( Tracking).

function Comparison

simply put, the so-called change can be reduced to two questions: The ① change has not occurred and the type of change, ② data from what changed to what. the difference between the two features of SQL SERVER change tracking is the answer to both questions.

Change Data Capture

Change-time data capture also answers the two questions above, which means that when we operate on a piece of data, it not only indicates that the record has been changed, but it also indicates the history of the data change. From the bottom-up implementation, this feature uses an asynchronous process to read the log table to minimize the impact on the system.

As shown, all changes made to a user table are stored in the corresponding change table, which provides a historical view of the change, documenting changes that have occurred over time:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4F/9E/wKiom1RhxfqyCWZwAAD0Q2MTyWE283.jpg "title=" 1.png " alt= "Wkiom1rhxfqycwzwaad0q2mtywe283.jpg"/>

Change Tracking

The change tracking feature only captures a record that has been changed, but does not record changes in the data so that the application can identify those records that have been changed and then get the latest data directly from the underlying table. This means that the change tracking feature is not able to answer some historical questions, but it is useful for applications that do not need to know that historical information only needs to know if the change is occurring, because it requires less storage space. Database utility an asynchronous tracking mechanism to track changes.

difference

The following table lists the differences between the two features, The CDC mechanism involves capturing changes from the transaction log, so the changes will not be captured until after DML operations;changetracking uses an asynchronous change tracking technique that is inline in DML operations, So the change information will be available immediately.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4F/9E/wKiom1RhxgPzlBZYAAC_PtacC1s494.jpg "title=" 2.png " alt= "Wkiom1rhxgpzlbzyaac_ptacc1s494.jpg"/>

SQL Server-change data tracking 1