Microsoft SQL Server 2008 R2 Unleashed- P170:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1634 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 tion interprets the SYS_CHANGE_COLUMNS bitmap value returned by the CHANGETABLE CHANGES . function and returns a 1 if the column was modified or 0 if it was not declare @last_synchronization_version bigint set @last_synchronization_version 0 SELECT as CustID TerritoryChanged CHANGE_TRACKING_IS_COLUMN_IN_MASK COLUMNPROPERTY OBJECT_ID MyCustomer TerritoryID1 ColumnId FROM CHANGETABLE CHANGES MyCustomer @last_synchronization_version AS CT go CustID TerritoryChanged SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS 4 1 U 0x0000000004000000 5 1 U 0x0000000004000000 In the query results you can see that both update operations SYS_CHANGE_OPERATION U modified the TerritoryID column TerritoryChanged 1 . Change Tracking Overhead Although Change Tracking has been optimized to minimize the performance overhead on DML operations it is important to know that there are some performance overhead and space requirements within the application databases when implementing Change Tracking. The performance overhead associated with using Change Tracking on a table is similar to the index maintenance overhead incurred for insert update and delete operations. For each row changed by a DML operation a row is added to the internal Change Tracking table. The amount of overhead incurred depends on various factors such as The number of primary key columns The amount of data being changed in the user table row The number of operations being performed in a transaction Whether column Change Tracking is enabled Change Tracking also consumes some space in the databases where it is enabled as well. Change Tracking data is stored in the following types of internal tables Internal change tables There is one internal change table for each user table that has Change Tracking enabled. Summary 1635 Internal transaction table There is one internal transaction table for the .
