Deferred updates are nowadays even more hot than they were 12 years back: today not only transactional replication uses the log reader's output, but also CDC uses the same output. Transaction replication's code was hidden for us end users, so we hardly needed to be aware of deferred updates. CDC however exposes our code to the deferred updates . The fn_cdc_get_net_changes_ functions are defined to return __$operation = 1 for deletes, __$operation = 2 for inserts, __$operation = 3 for an update's old value and __$operation = 4 for an update's new value. Some issues in the fn_cdc_get_net_changes_ functions in relation to deferred updates have been fixed, but not all of them (still surplus rows with __$operation = 1's can be returned). But the work arounds have come at a serious performance cost: fn_cdc_get_net_chages functions will choke-up on large(r) sets of changes in the _CT tables because at least 3 scans of the _CT tables are needed to do the workarounds correctly. Instead of putting work arounds in the fn_cdc_get_net_changes function, MS should be putting her resources onto having the log reader fixed to have it properly write __$operation 3 & 4 for all updates, instead of 1 & 2 for the deferred updates.
This thread shows how little knowledge is generally available on deferred updates. So I'd like to leave a link here to the only article I've found -albeit for SQL 7.0- that explains in detail what a deferred update actually is: http://sqlmag.com/sql-server/sql-server-70-update-strategies
Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]
If you don't have time to do it right, when will you have time to do it over?