NoLock VS ReadUncommitted Hints


NoLock and ReadUncommitted both are same. Both of it will leads to bad data being returned …!!!

Mostly, we are able to see ‘WITH (NOLOCK)’ next to table name,only to avoid the deadlock in SQL and result the query fast.
Suppose, if we have 20 tables in the query, each are joining with each other.
Then without thinking we starts adding the ‘WITH (NOLOCK)’.
We can also add ‘WITH (READUNCOMMITTED)’ next to table name.

Nolock-vs-ReadUncommitted |Academy4Code.com
Nolock-vs-ReadUncommitted |Academy4Code.com

Example:

SELECT * From TableAAA WITH(NOLOCK)
SELECT * From TableBBB WITH(READUNCOMMITTED)

If both reserved keyword having the same functionality and result, then why ‘READUNCOMMITTED’ introduced.
Inspite of being adding ‘WITH (NOLOCK)’ after each of the table. Simply set the isolation level to Readuncommitted for that specific connection.

Example:
SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED
SELECT * From TableAAA
SELECT * From TableBBB

Don’t need to write WITH(NOLOCK) and WITH(READUNCOMMITTED) in each statement.

For more info read atMSDN

Please loud at us if you have any query over this.
Please write us at ravindra@academy4Code.com OR academy4Code@gmail.com

Ravindra Kumar is a Programmer and an independent consultant from India. He has been a part of the industry for more than 6 years. During his career, he has worked on mutiple projects of the USA and of India (Technology Evangelist at Microsoft). He received his Bachelors of Engineering from Punjan Technical University. He has been a regular speaker of SQL Sessions in Indian IT Company.

For more, you can consult at ravindra@Academy4Code.com