Clever Geek Handbook

WTH (NOLOCK) syntax for subquery

I am trying to add with (nolock) in a report request that locks the full db on startup, making it impossible 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 a syntax error:

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

where is WITH (NOLOCK)

shuold placed?

I am not saying that this is the solution to all problems, this is just the test that I want.

Thank!

+3


source to share


3 answers


If there are more tables and more than one query involved, and you don't care about dirty reads, then set your transaction isolation level to read uncommited

instead of writing Nolock

everywhere



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
+2


source


I would put it here, but it should be noted that you are using a view, so it should go through the tables in the view:



SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB
+1


source


If you care about accuracy, you shouldn't put it in your report. This hint has some very interesting things that it does that many people don't fully understand. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/

But if you keep working, the table hints refer to the table. Of course, since this is an opinion, it won't help.

SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB
+1


source






More articles:


All Articles