sql server nolock hint syntax example and performance

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.

Conclusion

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.