Should I still use With(NoLock) in SQL Server queries?

Question

I'm trying to write some queries to fetch data from database. I used to use With(NoLock) to prevent SQL Server from placing shared locks on database. And recently a friend of mine told me that the default behavior now is that it doesn't put any lock on reading data.

However, I can't find a clean explanation for this. Should I still use With(NoLock) in SQL Server?

Answer 1

WITH (NOLOCK) behaves the same as setting the transaction isolation level to READ UNCOMMITTED, just its scope is different.

However, keep in mind that SQL Server is allowed to use a different isolation level depending on the type of query you are running. (INSERT, UPDATE, DELETE and other write operations have to run under at least READ COMMITTED).

Your friend is not right, SQL Server will acquire at least a schema lock during read operations on the table to prevent changes in the table's structure. This lock will be applied to the table even if you use READ UNCOMMITTED isolation level or the WITH (NOLOCK) hint.

In general, I would avoid using the WITH (NOLOCK) hint. You have less control when using the table hint. Use READ UNCOMMITTED isolation level for the connection if dirty reads are acceptable.

You can always change the isolation level of the connection, but you cannot dynamically remove the WITH (NOLOCK) hint. This is especially true when you use WITH (NOLOCK) on each and every table.