Using the NOLOCK Table Hint

Using the NOLOCK Table Hint

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Each time you write and execute a T-SQL Query in SQL Server, the Database Engine selects the most optimized query plan for executing the query.

The SQL Server Database Engine component that undertakes this task is the Query Optimizer. Given the T-SQL query itself as well as the underlying database(s) design, the Query Optimizer selects the best execution plan for the query and then it executes the query.

Now, in SQL Server you can make use of several query hints, such as locking hints. Though, as SQL Server Query Optimizer typically selects the best execution plan for a query, it is not recommended for inexperienced developers and administrators to make use of them.

Nevertheless, today I will talk about a special table hint, that is the NOLOCK hint. This hint is equivalent to the READUNCOMMITTED hint.

A typical example of using the NOLOCK hint is the following:

SELECT *
FROM TABLE_NAME WITH (NOLOCK)
GO


Similar examples:

SELECT *
FROM TABLE_NAME A WITH (NOLOCK)
GO

SELECT *
FROM TABLE_NAME (NOLOCK)
GO

SELECT *
FROM TABLE_NAME A (NOLOCK)
GO

For environments where many concurrent queries are executed against the same database objects (i.e. Web Applications), the NOLOCK hint can become quite handy. By using this hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. Of course, this means that the query may see inconsistent data, that is data not yet committed, etc. So, if this is not a problem for you, it might solve many concurrency issues.

Though note that in general is not a good idea to apply the above practice as a rule.

I hope you found this post useful!

Until next time!


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.