Search code examples
sql-serversql-server-2008-r2nolock

WTH (NOLOCK) syntax fo subquery

I am trying to add with (nolock) in a report query that when run locks the full db making imposssible for other users to use the db.

I can't figure out how to use in this case:

-- this is just an example:
SELECT FIELDS FROM (SELECT * FROM ATABLE) AS SUB

This gives syntax error:

SELECT FIELDS FROM (SELECT * FROM ATABLE) WITH (NOLOCK) AS SUB

where WITH (NOLOCK) shuold be put?

I am not saying this is a solution to all problems, it is just a test i want to.

Thanks!

Solution

  • If there are more tables involved and more than one query invloved and you don't care about dirty reads then set the Isolation level of your transaction to read uncommited instead of writing Nolock everywhere

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED