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?


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).

Ciaran Archer
+3  A: 

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"

Good point. If there was only benefit and no cost, the query optimizer would probably automatically do this for you and there wouldn't be an option. Be careful of general rules for things you should always/never do.
@JohnFx: well phrased!