First - this was a very good question. Learned something today which is great.
Shame about the (nolock) hints, though. They serve no purpose, and they might lead people to believe that these hints are okay to use. They are not, except in a very limited amount of situations.
What would these limited situations be?
You won't believe how many people that actually use NOLOCK or READ_UNCOMMITTED to "fix" concurrency and locking problems. One of our vendors use NOLOCK in every(!) SELECT statement in a system we have purchased from them. They have never bothered to explain the reason for doing it, but my guess, after doing some investigation, is that it is caused by two things:
- Lots of missing indexes, which results in excessive scans
- Bad programming, because when I save data in the front-end, SQL Profiler showed that before the transaction for the update statement was committed, another connection from my pc was opened trying to read the updated row(s).
How about thousands of connections hitting against high volume OLTP databases that are also required for up to the minute reporting where replication is not an issue nor are dirty reads?
There are times where hints are required. I may not like them as the norm, but I don't think that one should dismiss them all together.
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan