sql server nolock hint syntax example and performance
- Last Updated : 15/02/2022
SQL server nolock hint is used to override the default behavior of transaction isolation level. The default behavior of transaction isolation level is READ COMMITTED data, that means if you run update and select query at the same time your select query will wait until the update query finishes or commit the changes. In this case if you do not want to select data ignoring the update query you need to use sql server with nolock table hint.
why use nolock in sql
We use nolock in sql so that we can read data at the same time when someone tries to update data.
You may already know if you execute a select and update query at the same time, the select query will be blocked until the changes are committed, that means until the update query completes. So we use nolock hints in sql so that we can read data without any blocking issues.
sql server nolock syntax
Sql server nolock syntax is easy and straightforward, you just need to write WITH (NOLOCK) after the table name. If you use the alias of the table you need to write the nolock hint after the alias.
sql server nolock example:
SELECT * FROM CUSTOMERS WITH (NOLOCK)
sql server nolock with table alias example:
SELECT * FROM CUSTOMERS AS C WITH (NOLOCK)
sql server with nolock performance
One of the main reasons for using nolock hint in sql is its performance. If you use nolock hint in your select query your query will gain performance in certain scenarios. Let's have a look at a scenario.
Suppose user A runs a query to select all the data of your customer, and at the same time another user B runs another query to update all the customers data. In this case user A needs to wait until user B’s query is committed.
//user A without nolock hint (this query will blocked) SELECT * FROM CUSTOMER //user B updating the customer at the same time(this query will execute first) UPDATE CUSTOMER SET address = 'this is default address' //Select data with nolock hint(This query will read data without block) SELECT * FROM CUSTOMER WITH (NOLOCK)
So by using nolock table hint in sql select statement you can gain performance if at the same time anyone also runs update or delete query on the same dataset.
sql server with nolock join
You can use sql server nolock table hint in your join query. The syntax is the same: just put the WITH (NOLOCK) keyword after the table name or table alias name. That means you can easily use nolock tables in multiple tables.
SELECT C.NAME, O.TOTAL FROM CUSTOMER AS C WITH (NOLOCK) LEFT JOIN ORDER AS O WITH (NOLOCK) ON O.CUSTOMERID = C.CUSTOMERID
how to add nolock to a sp in sql server
Yes you can add nolock to a sp just like the normal way.
CREATE PROCEDURE GET_ALL_CUSTOMER_DATA AS BEGIN SELECT * FROM CUSTOMER WITH (NOLOCK) END
Disadvantages of using nolock in sql server
The only Disadvantages is that using the NOLOCK table hint you can get uncommitted “dirty” data. Suppose you run a query to read data and at the same time other users run a query to update the data. Now if you read before updating the data you may get dirty data.
Note: don’t use sql server nolock hint in update and delete queries.
Based On your business logic you need to use nolock table hint. If you have no problem with the darty data and you want to avoid the blocking issue you can use sql server nolock table hint.
Still you face problems, feel free to contact with me, I will try my best to help you.