>>You can do this...
>>
>>WITH (NOLOCK, INDEX (MyIndex))
>>
>>I'm not sure I'd advise using this approach in production - more of a design issue than anything else. But I think that syntax will work.
>
>Yes, I found that a few minutes ago. Are you saying NOLOCK should not be used at all in a select statement?

Anytime someone uses NOLOCK in production, they are risking a query returning uncommitted data.

A NOLOCK is very similar to a dirty/uncommitted read. You risk bringing back data that theoretically might never get committed, or might represent an incomplete picture.

Let's see you have 2 tables...Orderheader and order details

Someone is posting an update to orders, which includes changes to the line item details.

Depending on how those changes occur.....it's conceivable that if you query the data and use NOLOCK at the same time of the updates, you could return some of the results from the update, but not all of them....and even possibly for an individual order. So basically you've retrieved an incomplete picture. Obviously a very bad thing.

The risk is often low, but it can happen.

I'm not saying, "don't ever use NOLOCK in production"....if the general workflow is such that the situation I just described would never happen, and you need to retrieve data without getting any shared locks, then I suppose it's safe. But there are many people out there who say, "Don't ever use NOLOCK in production"....and I understand why they say it.

If you need to be able to return only good committed data - but also don't want the risk of deadlocks, you might want to look at the READ COMMITTED SNAPSHOT isolation level.