Archive for March, 2011

SQL Server 2008 – Change Tracking

March 11th, 2011 No comments

I have recently started studying for some Microsoft SQL Server exams (in particular 70-432). In order to reinforce some of the information, I thought it would be a good idea to blog about some of the features of SQL Server 2008 I learn about. This post will be on the built in mechanism for Change Tracking.

Change tracking is a relatively lightweight functionality that associates a version with each row in a table which has had CHANGE_TRACKING enabled on it.

By using this mechanism, it should be easy to read the version number when data is read from the database, and when it comes to writing data back, this version number can be checked to see if it has changed or not, allowing your application to determine whether it is safe to write data back or not, depending on how you handle the situation.

Once the CHANGE_TRACKING option has been enabled for a database, you can choose which tables in the database change tracking information is kept for.

Two other options can also be used. Namely CHANGE_RETENTION, which allows you to specify how long change tracking information should be captured for, and AUTO_CLEANUP, which allows change tracking information to automatically be cleaned up.

If anyone has any extra information or can clarify any of the above points, then please feel free to add a comment 🙂