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
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
http://www.sql-server-performance.com/stored_procedures.asp http://www.sql-server-performance.com/rd_create_stored_procedures.asp Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Begin and end does not aid performace like SQL Guess mentioned to you, the key words are just used for control of flow.