Change Capture in MS SQL Server… How Cool it is!!!

I too captured some!!!

Incremental loading in data warehouses has always been a trick (for me atleast!!). It involved designing tables to capture the changes, writing PL/SQL triggers to understand the changes etc.

However, with the pretty cool “Change Capture” mechanism built into the databases itself, much of the ETL developers pain has been relieved now.

So what is Change Capture?
It is a daemon that once given life, can capture inserts, updates, deletes (should I be saying all changes) to any database table on which it is enabled.

How does it work in MS SQL Server?
Change capture works using the transaction log in MS SQL Server. It picks up the changes on the being spied tables, written to the database’s transaction logs (So there might be a bit of latency involved).

And what shall I take notes on?
It is very important to understand few keywords associated with this mechanism. To me they are,

1. LSN
Expanded to Log Sequence Number; It is an incremental number assigned to every record in the transaction log of the database.

2. lsn_time_mapping ( )
It helps to map the LSN in the log, to the time the transaction committed. Yes, you are right!! Transaction logs do not capture the time of change, instead it just writes the LSN and we need to use this function to get the corresponding time.

3. Capture Instance
It consists of a change table (corresponding to the table on which change capture is enabled) and set of two functions; fn_cdc_get_all_changes_* which returns all changes on the table, fn_cdc_get_net_changes_* that returns just the nett changes on the table.

Would you like to dig a bit more deeper in to this cool CDC.. Visit SQL Guru Pinal’s blog at


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s