SQL SERVER – Applying NOLOCK Hint at Query Level – NOLOCK for whole Transaction

Just received very interesting question in email:

“How do I apply NOLOCK hint to my whole query. I know that I can use NOLOCK at every table level but I have many tables in my query and I want to apply the same to all the tables.

I want to do something like following script.

SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
   sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
OPTION (NOLOCK)

When I ran it it gives me following error:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘NOLOCK’.

Please recommend.”

I just never thought of using NOLOCK such a way, when I had to use NOLOCK in such a way, I always use isolation levels.

Here is how I use it:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate
-- Set isolation level to original isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Have you ever faced situation like this when you need to set NOLOCK isolation level at all the transaction level? If yes, how did you do this?

Here is the script to know your database isolation level: SQL SERVER – Check the Isolation Level with DBCC useroptions

Reference: Pinal Dave (https://blog.sqlauthority.com)