The options in tracking change data in SQL Server 2008

If you have already had the chance to study SQL Server 2008, you might have noticed that the database itself can help you with the old problem of finding changes made to the data. Yes, we already have triggers, but oftentimes you'd need to know afterwards, what tables or records have changed, and even how.

To help tackle this problem, SQL Server 2008 contains two features: Change Tracking and Chance Data Capture. Change Tracking is the simpler (and less resource-intensive) of the two, and provides an answer to the question, "has this row changed?" On the other hand, Change Data Capture (CDC) can answer this question, and also the question, "how has this row changed".

In effect, you could even use CDC as your "history" system, something like many ERP and version control systems provide. MSDN has a comparison of these two techniques available. Check it out.