I find the problem with ChangeTracking and ChangeDataCapture is that there is so much hardcoding going on. That means whenever you add or delete a column, it is another area that needs to be touched. Plus the queries are pretty unwieldly and verbose.

There is a need in my group to send out an email when anything changes among multiple tables. That would be a very long query, and pretty unwieldy.

I have a lot going on with dynamic SQL to do that, but there is a performance hit.

Thx everyone. It was fun writing this series. I need to finish the SQL Audit in the coming week so that I can send it to you guys for review before I submit. I have already submitted CDC and I am waiting for it to be reviewed.Andre, What I saw in CT is that it is using the old Tran replication technology to get the job done. For instance, if you add a column you have to do so many things to get the CT to track the changes just like replication of SQL 2000.

I'm thinking that one of the ideal business solutions to use CT is for database synchronization from mobile devices or off-site databases.

To help CT become more "auditable", maybe we could use the syntax:

WITH CHANGE_TRACKING_CONTEXT(@context)--<INSERT or UPDATE command here>

and send some information about the username or changes inside the @context VARBINARY(128) column. Is there a limitation for using the CT context and would it also become a major pain to maintain like described in the article?

Change Tracking comes with all SQL Server editions so it's a great little feature to have at our disposal.

"Data auditing is the process of doing a profile check and assessing the quality of data, to find how accurate it is. This can be achieved by keeping track of all the data changes."

A couple of points:

- you can't always know that data is correct. If the data is based on mathematical formula, and you have the inputs, you can. But you can't tell that my mobile telephone number is correct, or my date of birth. At least not without external validation. Quality is abstract.

- auditing goes beyond changing data. Tracking the viewing of data, or the printing of data, is very important. Examples can include celebrities who are hospitalized - you want to know if 300 hospital workers are viewing the patients chart (and shouldn't be).

I like this definition better: "[a] systematic examination of information use, resources and flows, with a verification by reference to both people and existing documents, in order to establish the extent to which they are contributing to an organisation’s objectives"