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.

FROM AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN AdventureWorks.Sales.SalesOrderHeader soh ON
   sod.SalesOrderID = soh.SalesOrderID
ORDER BY sod.ModifiedDate

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:

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

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)

SQL Scripts
Previous Post
SQL SERVER – Making Database to Read Only – Changing Database to Read/Write
Next Post
SQL SERVER – Speaking on T-SQL Worst Practices at Great Indian Developer Summit 2011 – Bangalore

Related Posts

13 Comments. Leave new

  • Lets say the query takes a lot of time to execute say 1 hr..then there would be impact in other places right??..
    So, What say?


  • @Pinal Dave you can use ‘nolock’ like this
    SELECT *
    FROM AdventureWorks.Sales.SalesOrderDetail as sod with(nolock)
    INNER JOIN AdventureWorks.Sales.SalesOrderHeader as soh with(nolock)
    ON sod.SalesOrderID = soh.SalesOrderID
    ORDER BY sod.ModifiedDate
    but it has two drawbacks
    1-another tansaction is rolled back while your query still running you will get this exception:
    Could not continue scan with NOLOCK due to data movement.
    2-another tansaction is rolled back after your query had ended
    then you have dirty data

    i recomment to use ‘readpast’ as the database engine not consider any locked rows ordata pages when returning results.

  • @Prawin, no it will not impact the other processes

    • If you have a long running query, odds are blocking side effect will be observed with NOLOCK. While bypassing locking might sound tempting, sometimes deadlock might be a better alternative

  • Sai Pavan Viswanath
    April 17, 2011 3:33 pm

    I have similar kind of requirement but I have to query production databases to run some scripts. Is it safe to work with transaction isolation levels on production databases.

    Sai Pavan Viswanath

  • deepraj singh
    April 17, 2011 11:08 pm

    great penal .very basic step

  • Hi Pinal,
    Most of the situtations where i have used NOLOCK is at the table level on reporting databases (part of Datawarehouse), very rarely use it in OLTP. Interesting viewpoint on using NOLOCK at transaction level.

  • Yes, I’d be interested to know if there is any impact on a production database when setting NOLOCK isolation level at the transaction level?

    If not, could you clarify the need to change it at the end of the query?


  • Thanks! you are a life savor, spent a while trying to figure out how to dynamically add to a generated nhibernate query.

  • I have a process which pulls the data and send SMS. My concern is, if i start inserting the data in the Table from where i read and send sms and the same time i run the application which send sms, it should not read the inserted data until its committed. But in the mean time it should fetch all the previously commited transaction. How can i achieve the save.

    INSERT INTO test_name VALUES (‘ABC’)

    SELECT * FROM test_name (Should return me data other then ‘ABC’ as its not committed yet.


Leave a ReplyCancel reply

Exit mobile version