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’.
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
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 (http://blog.sqlauthority.com)