SET NOCOUNT ON — When it is recommended | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SET NOCOUNT ON — When it is recommended

Hi all I have all procedures in Database Server,My Application reside in Application server
In this case my procedure required SET NOCOUNT ON,if it thire what was the problem,if it is not thire what was the problem
Please advance me.
Thanks in Advance
"If Iam Wrong suggest me Right"
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. So if you have SET NOCOUNT ON it won’t return you number of rows processed which reduce the network traffic —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at

You should use SET NOCOUNT ON always in your procs. It should be straight the first command in the proc. As Dinesh mentioned does it suppress the DONE_IN_PROC messages and can many times speed up procs considerably. —
Frank Kalis
Microsoft SQL Server MVP
As it is only available in parse-time option is set when the code is parsed, so it will be set even if the branch of code it’s in is never executed. A run-time option is set only when
that part of code really does execute. See "SET Options" in Books Online for more details. As you are in SQL 2005 following blogs should help about recompilation issue too:…op-stored-procedures-that-are-recompiled.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Use SET NOCOUNT ON is a good habit can speed up proc performance.