In my previous blog entry I talked about the new Change Data Capture (CDC) feature. This allows us to capture data changes to another relational table for later use. An example may be in order to apply incremental updates to a Data Warehouse. There are many strategies for this process that can be employed but Microsoft has given us the new MERGE statement which helps simplify things. Let’s take a look…
One way of applying incremental updates is by use of a Timestamp column to identify what has changed since the last incremental load. Another way is to examine the Primary Key if it is automatically generated by the system. But what about changes? Some of the processing logic can get quite complex. The MERGE statement will help us here. We can name a target table and a source table. The source table contains the latest changed rows and typically will match the structure of the target table. Matching criteria is provided with the ON clause much like within a JOIN. Based on the matching criteria the system can evaluate each row in the source table and determine if we have a match with the target table. Then we can specify what to do when a match occurs using the WHEN MATCHED clause. For instance, you may choose to update that row in the target table. If not specified, no processing occurs for that row. You can also specify WHEN NOT MATCHED. Typically, you would request an INSERT when no match was found in the target table. The nice thing about the MERGE statement is that duplicates do not create Primary Key violations. So you can control Insert, Update and Delete processing from a single statement.
So how do you get the data changes into the source table in the first place? That’s where CDC comes in. Both CDC and the MERGE statement are primarily for the Data Warehouse environment in terms of simplifying the incremental load process from an operational database. But I am sure you can think of all sorts of applications for these cool new features. As always, the trade off is extra storage.
Storage is relatively cheap and easy to come by these days so no worries…hang on, didn’t we say that about financial credit a few months back…something to think about…
Later
Brian
Recent posts
Data Partitioning is even better in 2008
