Recently I was talking with Vinod Kumar regarding NOLOCK. Suddenly he asked me do I know what kind of lock WITH(NOLOCK) hint takes on object. The immediate response of mine was that NOLOCK does not take any lock. He responded suggesting that I should think more and answer.
I realized right after his suggestion to think harder and I said Schema Lock. Yes, WITH(NOLOCK) hint takes Schema Lock on the object which is accessed. Here is the script to prove it.
Step 1: Run following script with query hint NOLOCK
FROM sys.all_objects a WITH (NOLOCK)
CROSS JOIN sys.all_objects b WITH (NOLOCK)
Step 2: Check Lock on the object using DMV
SELECT resource_database_id, request_mode, request_type, *
You will clearly see from resultset that NOLOCK takes Schema Lock. Well it is very natural but the name NOLOCK sometime makes us to make incorrect statement.
Please also read great blog post by Robert Davis on The Truth about NOLOCK Hints.
Reference : Pinal Dave (http://blog.SQLAuthority.com)