SQL Server Performance

WITH (NOLOCK) and table-vars

Discussion in 'T-SQL Performance Tuning for Developers' started by frettmaestro, Sep 15, 2003.

  1. frettmaestro New Member

    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"
  2. gaurav_bindlish New Member

    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
  3. Chappy New Member

    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?
  4. frettmaestro New Member

    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"
  5. Twan New Member

    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
  6. frettmaestro New Member

    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"
  7. frettmaestro New Member

    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"

Share This Page