SET NOCOUNT ON OPTION

[Leon]

DOES SETTING NOCOUNT ON option affect my stored procedures and website
performance in anyway within my asp.net application?

When and when not to set SET NOCOUNT ON?

I know what it does but not exactly where and when I want to use it.

i.e.
CREATE PROCEDURE GetAuthorization 2.0
@Username Varchar( 20 ),
@Password Varchar( 16 )
as
SET NOCOUNT ON
declare @AccountID int

set @AccountID = -1
select @AccountID =
case
when Password = @Password and Active = 1 then AccountID
when Password = @Password then -2
else -3
end
from Account
where Username = @Username

return @AccountID
go

 

[Cirrosi]

A sp is able to return motre than one resultset.
Even an update or an insert returns resultset, but in many case you don't
need that.
With nocount option you obtain only the resultset relative to last query.

 

[Leon]

So is it good or bad to use SET NOCOUNT ON?
use my example sp for explanation. Thanks!

 

[Cirrosi]

I tink that is better using it.
It avoid that a procedure return more than one resultset, then it should
execute quickly.
Your sp don't return any resultset than you shoul use it.

Excuse me for my bad english.

 

[Leon]

Thank Again!

I tink that is better using it.
It avoid that a procedure return more than one resultset, then it should
execute quickly.
Your sp don't return any resultset than you shoul use it.

Excuse me for my bad english.

 

[Kikoz]

Hi all.

Open Query Analyzer. Execute any of your sprocs that don't have SET NOCOUNT
ON. You'll have your results (if it returns any recordsets) and a message
saying something like "(n row(s) affected)". To get this message SqlServer
needs to make two trips. Insert SET NOCOUNT ON into the text of your sproc
right after AS, save and execute it again. There will be no messages and no
additional trips this time resulting in a faster response. That's the
difference.

 

[Leon]

Thanks Kikoz!

Hi all.

Open Query Analyzer. Execute any of your sprocs that don't have SET
NOCOUNT ON. You'll have your results (if it returns any recordsets) and a
message saying something like "(n row(s) affected)". To get this message
SqlServer needs to make two trips. Insert SET NOCOUNT ON into the text of
your sproc right after AS, save and execute it again. There will be no
messages and no additional trips this time resulting in a faster response.
That's the difference.

 

[Cirrosi]

From T-SQL Manual:
Syntax
SET NOCOUNT { ON | OFF }

Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL ServerT to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.