1

In using SQL Server's Change Data Capture, I notice that the fn_cdc_get_all_changes_... table valued functions all use NOLOCK when querying against the capture tables. Is this a recommended practice when querying against the capture tables and if so, why?

1

It appears that this was added in the 2008 time period. You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at:

http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf

It is interesting that the request was made to prevent the CDC updates from blocking the CDC queries that select the data. This change was posted to the Connect item on 3/18/2008.

Considering the potential velocity of change, this may make sense for this case, but it does not make NOLOCK a generally good idea for other code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.