Now that you have tried the script I presented last wednesday on your production server(s)…
Hi,
at the latest PASS conference i Seattle I took part in several sessions on change tracking, but was very disappointed that every single one was dedicated to the CDC technology of change tracking.
Even when I remarked that there is a easier AND cheaper solution available, the presenters did not seem to know about it. Thus, I had to hold “mini sessions” after the CDC sessions where I explained to some of the audience that Change Tracking (CT) is also available as a change tracking method. The advantage with this method is that it is available on all versions of SQL Server (whereas CDC does demand the Enterprise $$$ license). Also CT only tracks net changes to data, not the full state shifting that CDC tracks by reading tha transaction log. In the normal scenario where you want to UPDATE the changed data rows only (through a MERGE), you do not care about intermediate states of you data rows, and CDC could be considered an overkill for the output you need.
CT works just like adding that extra rowversion column but only you do not have to do it in the table itself, it is done through SQL Server hidden system tables, which means that you do not have to modify your (3rd party?) data model to make CT possible!! Very appealing indeed.
I will not present the details in CT here, just really emphasize that this solution exists, that it is available, and also that it is very easy to set up.
Read Books Online, you will be surprised!
/Joakim Nyström