SQL Server Performance

Stored Procedures

Discussion in 'T-SQL Performance Tuning for Developers' started by sssmurali, Jun 29, 2004.

  1. sssmurali New Member

    Hi,

    Will it really improve performance if we include BEGIN and END statements for a stored procedure, even if the SP is a SELECT statement?

    Also, what is the use of WITH (NOLOCK) option in SELECT statements and how it improves performance?

    Thanks!

    Best Regards,
    Murali S
  2. SQL_Guess New Member

    imho, the BEGIN and END shouldn't aid performance (i've never heard of it doing that and I can't see why it would help).

    The "with (nolock)" tells SQL to allow the query to ignore any locks on the table, and read the "dirty" data as it is on the table at that point in time. This means no waiting for X locks to be released, and also no creating of S locks that might block an intended X locks. That said, it also means you may be reading inconsistent data - use with care and understanding.

    CiaO

    Panic, Chaos, Disorder ... my work here is done --unknown
  3. satya Moderator

  4. Raulie New Member

    Begin and end does not aid performace like SQL Guess mentioned to you, the key words are just used for control of flow.

Share This Page