Microsoft® SQL Server® 2012 Bible

953

Chapter 41: Data Change Tracking and Capture

41

FROM ChangeTable (Changes HumanResources.Department, 1) as CT

LEFT OUTER JOIN HumanResources.Department d

ON d.DepartmentID = CT.DepartmentID

ORDER BY CT.SYS_CHANGE_VERSION;

Result:

Version DepartmentID Op Name GroupName

-------- ------------ --- ------------- -------------

2 17 U Changed Name PASS Summit

As expected, the result shows row 17 being updated, so there’s no data other than the

primary key returned by the ChangeTable data source. The join pulls in the data from

HumanResources.Department.

Removing Change Tracking

If you fi nd that Change Tracking is causing performance problems or just not accomplish-

ing what you want it to, it’s as easy to remove Change Tracking as it is to enable it. All you

have to do is disable it from every table, and then remove it from the database.

If the goal is to reduce Change Tracking by a single table, then the same ALTER command

that enabled Change Tracking can disable it:

ALTER TABLE HumanResources.Department

Disable Change_tracking;

When Change Tracking is disabled from a table, all stored ChangeTable data — the PKs

and columns updated — are lost.

If the goal is to remove Change Tracking from the database, then Change Tracking must

fi rst be removed from every table in the database. One way to accomplish this is to leverage

the sp_MSforeachtable stored procedure:

EXEC sp_MSforeachtable

'ALTER TABLE?

Disable Change_tracking;';

However, after much testing, in many cases sp_msforeachtable often fails to remove

Change Tracking from every table.

A less elegant, but more reliable, method to ensure that Change Tracking is completely

removed from every table in the database is to actually cursor through the sys.change_

tracking_tables table:

DECLARE @SQL NVARCHAR(MAX)='';

SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.' + t.name +

' Disable Change_tracking;'

FROM sys.change_tracking_tables ct

c41.indd 953c41.indd 953 7/31/2012 10:17:21 AM7/31/2012 10:17:21 AM

http://www.it-ebooks.info