SQL Server-Change tracking 2

as the previous article says, SQL Server provides two capabilities to implement change tracking, and this section focuses on changing Tracking functionality.

ActivateChangetracking

we can activate and use the following steps Changetracking Features:

1. Activate change Tracking at the database level

use the following statement to activate the feature on the database ( or activated in SSMS 's Properties pop-up box ) , its two parameters indicate the lifetime of the change information and whether the automatic purge Change information task is activated:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4F/D5/wKiom1Rh3SuCVY0NAABdRBsT41s660.jpg "title=" 1.png " alt= "Wkiom1rh3sucvy0naabdrbst41s660.jpg"/>

2. Activate the change Tracking on the desired table

we need to perform the activation on each table where we want to track changes. ( Note that the table must have a primary key in order to use the Change tracking feature ) , you can use the following statement or use the table properties popup:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4F/D4/wKioL1Rh3Z_x1I2OAABZozSub8A008.jpg "title=" 2.png " alt= "Wkiol1rh3z_x1i2oaabzozsub8a008.jpg"/>

where the parameters track_columns_updated defaults to Falsebecause additional storage overhead is required to maintain column update information.

3. Understand the cost of change Tracking

Opens the Change Tracking has some impact on database operations, and we can view their specific impact from administrative operations,DML operations, and storage aspects:

1. Manage operations

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4F/D4/wKioL1Rh3a6gQyy2AAHiFOf1mg4612.jpg "title=" 3.png " alt= "Wkiol1rh3a6gqyy2aahifof1mg4612.jpg"/>

1. DML Operations

2. Store

UseChangetracking

when this feature is activated, it is time to use it, there will be a version counter on the database where the feature is activated, and then every time the change tracking is activated on the table DML Operations produce a new version number associated with it, and in some ways the revision number of the tracking is somewhat similar to the rowversion data type.

Now we can easily imagine how to use change tracking:

1. First, the application loads the initial data from the database.

2. and then get the Version number and record it as the starting point for the next data pump

3. then, the next time the application needs to do incremental data extraction from the database, provide the last recorded Version number and just load the new changes

4. If, unfortunately, the application has been cleared of changes between the last incremental and the next incremental extraction, the application has to redo the full data load to prevent inconsistent data

The database provides a function to implement the functions mentioned above:

1. change_tracking_current_version ()

get the latest changes in the current Version number

2. changetable (Changes ...)

the rowset function returns the change information, which accepts the indicated and Version number as a parameter, and then returns all change information from the table in which the change information was internally tracked for that user table since the version number was provided

3. change_tracking_min_valid_version ()

The function requires the object_id of a tableand then returns the smallest valid Version number in the current database . The application should determine whether the last loaded version number is less than the current valid version number before each incremental extraction, and if so it means that some of the change information has been erased for some reason, and must be done once full data is loaded

SQL Server-Change tracking 2