WITH (NOLOCK) and table-vars | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

WITH (NOLOCK) and table-vars

I have a somewhat deep select with 9 joins total and one of them beeing a table-variable. It works just fine but when I try to add WITH (NOLOCK) I get the error "Incorrect syntax near the keyword ‘WITH’" so something tells me that locking hints can’t be used with table-variables. Is this really the case? —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
I haven’t come across such scenario. But it would be interesting to know what others have to say in this. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Does it make any sense to apply locking hints to a table variable? Since they have limited scope (ie local), by definition they do not have the overhead of concurrency to worry about. Or do you mean the NOLOCK hint cant be applied to other tables, in a join query in which a table variable participates?
I want to apply the NOLOCK-hint to the other 8 tables in the join if possible…it’s a rather deep and expensive join and I would *really* like it not to issue any locks. The quality of the data isn’t all that important… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
Hi there, You can’t put @table with (nolock), but you should be able to add it to all other tables in the same query. The @table won’t ever use locking since it is not a real table and can only be used by this one connection anyway Cheers
Twan
So I need to specify WITH (NOLOCK) for all tables in the join then? I guess it’s worth a try… —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"
I put WITH (NOLOCK) after all the tables in the join instead and it worked like a charm, thanx fellas <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />–<br />Frettmaestro<br />"Real programmers don’t document, if it was hard to write it should be hard to understand"
]]>