This article, discusses about the NOLOCK Table Hint in SQL Server.
About Query Optimizer in SQL Server
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.
Query Hints in SQL Server
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
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!
Learn more tips like this!
Enroll to our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Development Articles:
- Error converting data type varchar to float
- Error converting data type varchar to numeric
- The set identity_insert Command in SQL Server
- Handling NULL Character x00 when Exporting to File Using BCP
- The Net.Tcp Port Sharing Service service on Local Computer started and then stopped
- …more SQL Server development articles
Subscribe to our newsletter and stay up to date!
Subscribe to our YouTube channel (SQLNetHubTV)!
Check our SQL Server Administration articles.
Check out our latest software releases!
Check our eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.