470,602 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,602 developers. It's quick & easy.

SET NOCOUNT ON OPTION

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

Nov 18 '05 #1
7 2422
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" <vn*****@msn.com> ha scritto nel messaggio
news:OV**************@TK2MSFTNGP10.phx.gbl...
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

Nov 18 '05 #2
So is it good or bad to use SET NOCOUNT ON?
use my example sp for explanation. Thanks!

"Cirrosi" <Ci****************@fastwebnet.it> wrote in message
news:66******************@tornado.fastwebnet.it...
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" <vn*****@msn.com> ha scritto nel messaggio
news:OV**************@TK2MSFTNGP10.phx.gbl...
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


Nov 18 '05 #3
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" <vn*****@msn.com> ha scritto nel messaggio
news:ue**************@TK2MSFTNGP09.phx.gbl...
So is it good or bad to use SET NOCOUNT ON?
use my example sp for explanation. Thanks!

"Cirrosi" <Ci****************@fastwebnet.it> wrote in message
news:66******************@tornado.fastwebnet.it...
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" <vn*****@msn.com> ha scritto nel messaggio
news:OV**************@TK2MSFTNGP10.phx.gbl...
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



Nov 18 '05 #4
Thank Again!

"Cirrosi" <Ci****************@fastwebnet.it> wrote in message
news:Ds*******************@tornado.fastwebnet.it.. .
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" <vn*****@msn.com> ha scritto nel messaggio
news:ue**************@TK2MSFTNGP09.phx.gbl...
So is it good or bad to use SET NOCOUNT ON?
use my example sp for explanation. Thanks!

"Cirrosi" <Ci****************@fastwebnet.it> wrote in message
news:66******************@tornado.fastwebnet.it...
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" <vn*****@msn.com> ha scritto nel messaggio
news:OV**************@TK2MSFTNGP10.phx.gbl...
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




Nov 18 '05 #5
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" <vn*****@msn.com> wrote in message
news:OV**************@TK2MSFTNGP10.phx.gbl...
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

Nov 18 '05 #6
Thanks Kikoz!

"Kikoz" <ki***@hotmail.com> wrote in message
news:ea*************@TK2MSFTNGP10.phx.gbl...
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" <vn*****@msn.com> wrote in message
news:OV**************@TK2MSFTNGP10.phx.gbl...
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


Nov 18 '05 #7
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.

"Leon" <vn*****@msn.com> ha scritto nel messaggio news:OV**************@TK2MSFTNGP10.phx.gbl...
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


Nov 18 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by GM | last post: by
3 posts views Thread by Rithish Saralaya | last post: by
3 posts views Thread by Iain Hallam | last post: by
3 posts views Thread by Stewart | last post: by
3 posts views Thread by Rob Meade | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.