5

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!

UnDiUdin
  • 14,924
  • 39
  • 151
  • 249

3 Answers3

3

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
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • in my context if before the SELECT statement i write this command i obtain the same result- i just made a test . thanks. – UnDiUdin Jun 16 '17 at 15:45
1

I would put it here but the thing to note is you are using a view so really it should go on the tables in the view:

SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB
Jesse
  • 865
  • 10
  • 18
1

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

But if you are deadset on continuing, table hints belong next to the table. Of course since this is a view it isn't going to help much. 

SELECT FIELDS FROM (SELECT * FROM MYVIEW WITH (NOLOCK)) AS SUB
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • Thanks. I modified the example using ATABLE instead than MYVIEW, now the syntax is fine thanks. I now about the risks of no lock but in my context i know it is an acceptable risk to take because of the context. – UnDiUdin Jun 16 '17 at 15:39
  • Cool. Sometimes for reports having the results be close is ok. – Sean Lange Jun 16 '17 at 15:50