March 2, 2021
Hot Topics:

Using SQL Server 2008 Change Data

  • By Jani Järvinen
  • Send Email »
  • More Articles »
You've written a nice database application that allows the user to manipulate data in an SQL Server database. All is good, but the users would also like to see how the data in the database has changed over time. For instance, John wants to know how the customer records changed last week and Jane would like to know how those details were added into her latest sales order.

Technically speaking, keeping such a log inside your application is quite straight forward. If you have a properly architected layered application, you could add logging code into your data access layer (DAL), and then construct a user interface for this log. But, adding such capability to each and every operation and table is often time-consuming work. Wouldn't it be great if the database you use could help you in this common, but often times taxing work?

Enter SQL Server 2008 Change Data

If you are using Microsoft's latest SQL Server version, then the database can help. SQL Server 2008 Enterprise Edition (Developer Edition also works, as it's based on Enterprise) supports two features that are collectively called Change Data. More precisely, these features are named Change Tracking (CT) and Change Data Capture (CDC).

Both services have the same aim: to help developers easily collect information about changes in the database data. Of the two features, Change Tracking is a simpler one and is only able to record that something has changed in the database. It supports the familiar DML statements: INSERT, UPDATE and DELETE. Change Data Capture on the other hand is able to record that something changed just like Change Tracking, but also how the data changed.

These features are useful in creating a change log, but also when synchronizing data between database instances. For example, you might need to periodically synchronize data between a local database and a central database. The Change Data features can help you do that.

Of course, being able to use the change tracking features requires some work on your part. First, you need to enable the change data collection features in your database. Second, you need to write code to retrieve the change data and process it. Additionally, you also need to understand the consequences of enabling change data, as both performance and database storage considerations must be kept in mind. For small databases, these overheads might not cause you to spend sleepless nights, but with larger databases you need to be more careful.

Using Change Tracking

If you are happy to live with the inherent overhead, using change data collection features is a good way to save the trouble of implementing these by hand. The easier of the two related features is Change Tracking, which is enabled for each database and table you want to track. Change Tracking can tell which rows in which table have changed. But on the other hand, Change Tracking data won't tell you how the data changed: you cannot use it to retrieve the older data back. You just know for instance that column N changed while doing an UPDATE, but no more.

But even with these limitations, Change Tracking can be useful. To get started with the feature, you first need a suitable SQL Server database that has been created at least with the compatibility level of 90 (this means SQL Server 2005 or later database, SQL Server 2008 databases by default have the compatibility level of 100). For testing purposes, you might even wish to create a completely new database. The next step is to enable Change Tracking. You can either do this from the database's properties in SQL Server Management Studio (see Figure 1) or by running the following T-SQL statement:

Figure 1. Change Tracking can be enabled from
database's properties in SQL Server Management
Click here for larger image

Figure 1. Change Tracking can be enabled from database's properties in SQL Server Management Studio.


After enabling the feature on a database level, you need to enable change tracking for each table you wish to monitor. This is done with the following SQL statement:


Just as with the database level setting, you can also enable change tracking from the SQL Server Management Studio. The setting can be found from the properties dialog box for each table. Remember that in addition to typing these scripts by hand, you can let SQL Server Management Studio help you. Its Template Explorer pane contains numerous ready-made scripts that you can use with little modifications. This applies to change tracking and change data capture functions as well (Figure 2).

Figure 2. Management Studio's Template Explorer has
ready-written scripts to help using change tracking
Figure 2. Management Studio's Template Explorer has ready-written scripts to help use change tracking functions.

When you enable change tracking and start adding, deleting or modifying records in the table for which you have enabled tracking, you can immediately use the built-in functions to read the logs. SQL Server 2008 provides several of these functions, most of which are provided as table- value functions, or TVFs. A table-value function is a regular function, but instead of returning a single value like a scalar function, these functions return a table's worth of data: zero or more rows.

Page 1 of 4

This article was originally published on April 14, 2009

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date