One of the interesting new features in SQL Server 2008 is Change Data Capture. I wrote about Change Tracking in a previous blog entry. That will track the what, when and who but will not tell you exactly what the data values were before and after an update. If you need to know the column data involved in an insert, update or delete statement then CDC is for you. For instance, you can use CDC in your incremental load package that copies changes to your Data Warehouse every night. Let’s look at this feature…
First of all, the CDC feature is only available in the Enterprise Edition (and the Developer Edition which is the same bits with a different EULA, you just agree not to put anything into production). So Microsoft rates this as an enterprise feature and wants you to pay for it. You will need to also buy off on the extra disk space that it will consume, because SQL Server will set up a CDC table for each table that you wish to capture changes for, and will save rows for each insert, update and delete. Depending on how many updates occur against the enabled tables, this may be significant. There’s also the extra processing to write the extra rows. Assuming this is all acceptable, CDC can be a big plus.
To enable CDC on a database you use the system stored procedure sp_cdc_enable_db. To identify a table that will capture changes, you use sp_cdc_enable_table. One of the options here is @supports_net_changes which, when set to 1 will allow you to view net results when multiple updates are made to the same row. A special system table is created for each table enabled, within the special CDC schema in the enabled database.
In my tests, I enabled the FactInternetSales table in the AdventureWorksDW2008 sample database. SQL Server generated a table in the CDC schema named dbo_FactInternetSalees_CT to capture changes. The CDC table contained all the columns from the source table plus 5 extra metadata columns to identify such things as the Log Sequence Number (LSN) and the operation code (1 for delete, 2 for insert, 3 for before an update, 4 for after the update). For each insert and delete on the source table, a row was generated in the CDC table. For each update, 2 rows were generated, one for before the update and one for after so you can clearly see what data has changed. SQL Server also generates special system functions that enable you to “get all changes” or “get net changes”.
As you can imagine, the CDC tables can get quite large, quite quickly but since storage is relatively cheap these days, this is an elegant solution to capture data changes without the need for triggers or timestamp columns. You might be happy to hear that CDC is disabled by default but if you need it, just turn it on.
Cheers
Brian
Recent posts:
Don’t play the victim – use the SQL Profiler deadlock graph
