Microsoft® SQL Server® 2008 Bible

Chapter 59. Change Tracking

IN THIS CHAPTER

• Lightweight synchronization for data warehouse ETL and mobile applications

• Net changes

• Auto cleanup

Change Tracking is one of the best-named software features I've come across. All Change Tracking does is track changes; it says to the world: "This row was changed, here's the PK." Clean and simple, no fuss, no muss. It's a piece of cake to configure and easy to query.

While Change Data Capture (another of the new auditing technologies in SQL Server 2008, covered in the next chapter) is limited to only the Enterprise Edition, Change Tracking is available in all the SQL Server editions, even SQL Server Express.

Change Tracking occurs synchronously within the transaction. It simply records in an internal table the primary key values of the rows that are modified. Although there's a cost to recording the changes within the transaction, it means that SQL Agent is not required.

Optionally, Change Tracking can store which columns were changed, using a bit-mapped method similar to how triggers know which column was included in the DML code.

The real purpose of Change Tracking is to support synchronization. By easily and reliably recording the primary keys of which rows were inserted, updated, or deleted since the last synchronization, it becomes much simpler to perform the synchronization.

Change Tracking returns the net changes. If a row is inserted and updated since the last synchronization, then Change Tracking will list it as an insert. If the row is ...