SQL 2008 - Do I use NOLOCK on a simple select statement?

Question

I'm linking to a 3rd party SQL database where I only select information out, never update/delete.

This 3rd party told me to always use NOLOCK when I'm selecting out of their database. Some other people I work with said don't worry about using it.

Does the WITH (NOLOCK) affect much if my select statements are going to execute in under 1 second?

Should I do it just for good practice?

Answer 1

Quick answer:

If you are selecting from a table that is very busy (and you may not know how busy this third party database is) then I would err on the side of caution and use the locking hint.

I can tell you from experience that on busy transactional databases, locking hints work and could be a big contributer to getting your query under a second (along with other optimisations).

Answer 2

NOLOCK will help to prevent your query from slowing down the vendor's application. However, you could get bad results back, see: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx or just google "danger of nolock"