Using SQL Server 2008 Change Data, Page 2
Testing the Sample ApplicationThe example application accompanying this article shows how you can access change tracking values from a WinForms application written with C# (Figure 3). The sample application is divided into two parts, one for the Change Tracking feature and another for Change Data Capture. Presently, let's focus on the Change Tracking part.
Click here for larger image
Figure 3. The sample application supports reading change data logs.
This part contains two buttons labeled Get Tracking Numbers and Get Tracking Changes. Whenever Change Tracking is enabled for a table, each change increments a so-called tracking version number. When requesting changes thru the special SQL Server change tracking functions, you need to pass in this tracking version number. For instance, if you update column A on row number 8, the maximum tracking number will increment by one. Then, if you request information about the latest change, an ID value will indicate that row 8 was changed. Furthermore, a bit mask will indicate which columns changed.
To retrieve the latest tracking numbers, you can use an SQL statement like this:
SELECT OBJECT_ID(N'dbo.MyTable'), CHANGE_TRACKING_MIN_VALID_VERSION( OBJECT_ID(N'dbo.MyTable')), CHANGE_TRACKING_CURRENT_VERSION()
This query would return the minimum and maximum valid tracking version numbers. For example, the values might be 0 and 6. This would mean that there are six versions of the table data, i.e. the records have changed six times. You can then use the tracking functions to retrieve changes with version numbers 0..5. The idea is that the sixth version is the latest data, which you can retrieve directly from the original table.
In the sample application, the above SQL statement is executed and the resulting two version numbers (plus the object ID) are displayed to the user (Figure 4). Note how the table name must be changed to an object ID before it can be passed to the CHANGE_TRACKING_MIN_VALID_VERSION function. This can be done with the OBJECT_ID function. Note also that in the sample application, the user can type in a table name; this is concatenated at runtime to a full SQL statement.
Figure 4. Displaying the minimum and maximum tracking numbers.
The next step is to utilize these version numbers to retrieve the change rows. The sample application uses an SQL statement like the following to retrieve the change tracking information given a tracking version number:
SELECT CT.ID, CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT FROM CHANGETABLE(CHANGES dbo.MyTable, 123) AS CT
Here, the table-value function called CHANGETABLE is used to retrieve the change records for the table dbo.MyTable with the tracking version number 123. For clarity, the table is aliased as "CT". The ID field returns the original ID value of the record that was changed. This means that the table which is to be monitored must have a primary key set. An identity field with an integer value is always a good candidate for a primary key.
The SYS_CHANGE_OPERATION column indicates the operation that took place: I for insert, U for update or D for delete. The SYS_CHANGE_COLUMNS value is on the other hand a binary field, which contains a bitmask of the changed columns. However, it's not fully documented, but its contents appear to be formed of DWORD values, where each value indicates the changed column starting from one. For example, if the bit values would be 0x00000003000000005, then the third and fifth columns would have changed. Note that for insert and delete operations, this bitmask is always NULL. The fourth field, SYS_CHANGE_CONTEXT reflects the table hint value specified using the WITH clause in an INSERT, DELETE or UPDATE statement. Otherwise the value is NULL.
The sample application converts the bitmask into column names based on a fixed array of strings. If you wanted, you could add a feature that reads the metadata of the table in question and then retrieves the column names based on that. This way, you could have a completely generic solution for converting the bitmask into column names.