Both. As far as I know NOLOCK or READ UNCOMMITED places no locks and it ignores all existing locks so you can get all sorts of wierd behavior becasue you don't know what state the data is in (deleted, uncommited, etc) This leads to possible duplicate or even missing data.
No lock takes no locks (other than schema stability) and honours no locks (other than schema modification). It's a way to tell SQL 'Get me my data, regardless of anyone doing anything with it, I don't care if the data is slightly inaccurate'
Neither, you'll get a syntax error </pedantic mode>
In 99% of the cases of NOLOCK being used in the consultancy work I have done it has been used by people who did not know the consequences and used it to fix a problem - usually DEADLOCK - and made no provision for the dirty rears issues, had no means of testing the scenarios that it occasionally raises, and could not reproduce the errors that annoyed customers / users reported to their support department.
A better quick-fix (from SQL2005 onwards) is to use READ_COMMITTED_SNAPSHOT but, as ever, test first
I agree that the READ_COMMITTED_SNAPSHOT would be ideal. But we have some clients still using 2000.
As pointed out, the no-locks were used to avoid Deadlocks. But some thought that no-lock avoids placing locks but honours exising locks, shows how things get misunderstood. I haven't heard of any reported problems from the customer related to using no-lock. Maybe because we have quick transactions and few rollbacks. Of course just because problems are reported does not rule out that they haven't been happening.
We still have a few select statements that are called outside of transactions and do not have no-lock and then get involved in Deadlocks. (I mentioned this in a previous post: ) I still can't understand how that can happen.