Understanding NOLOCK Query Hint

In our day to day T-SQL querying we use lot of query hints to modify the way a particular query will be executed. When we specify query hint SQL Server produces optimized plan using this query hint. This can be dangerous if it is not tested before in UAT as it is known fact that query plan which SQL Server makes using optimizer, which is its prized possession, is the best. Algorithm written for optimizer at low level is not known to ordinary people, how it makes best/optimized, most cost effective plan is not known to outside world but we know it does.
Query hints specify that the indicated hints should be used throughout the query and they affect all operators in the statement. One such query hint is NOLOCK. As the name suggests many users feel, that when this hint is specified in the query, the operation does not takes lock. This is not the case!

I will demonstrate it using simple query. I create a simple table with e_id as PK col, name, address and cell no.

Begin tran

select * from dbo.employee where e_id='a1'

exec sp_lock

If you see below this transaction has SPID 55 which is ID for the code which is just executed. It has taken two locks IS,S

In Mode Column
S=Shared lock
IS=Intent Shared

In type column
DB=databse
TAB=table

Now let us run same query with NOLOCK query hint and see if it actually takes any lock

Begin tran

select * from dbo.employee with (NOLOCK) where e_id='a1'

exec sp_lock

As you can see same lock is taken on the same table (see Objid in both fig they are same 1131151075) . IS and S.
So point is what is difference between query execution one which is given with NOLOCK and one which is not given with any nolock query hint.

Difference comes when both are trying to select data from table which has taken exclusive lock, I mean to say difference comes when query is trying to access table which is locked by INSERT/UPDATE statement.
I will show this with query>let us run an update command on the same table for the same row.

begin tran

update dbo.employee set e_name='SHASHANK' where e_id='a1'

exec sp_lock

Now i run same above queries qury 1 and query 2

Query 1

Now other query which is not using any Query hint

Now we see the difference: query with NOLOCK query hint produced output but simple query with no hint is not producing any output. It is blocked and that can be seen by running SP_Who2, I ran this query and result is below:

As you can see SPID 56 is blocking SPID 55. Then I ran DBCC INPUTBUFFER command to find out text corresponding to these SPID's, below is the result:

From above query output it is clear that when we use NOLOCK query hint, transaction can read data from table which is locked by Update/insert/delete statement by taking the exclusive lock (exclusive lock is not compatible with any other lock). But if in same transaction we don't use query hint (NOLOCK) it will be blocked by update statement.
Drawback of NOLOCK is dirty read. So it is not advised to use it in production environment. But it can be used to read data from a table partition which won't be updated when this select is running. Like you can run query to select data from Table partition containing Jan 2013 data summing no records will be updated for January.