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
@dotnet.itags.org.Username Varchar( 20 ),
@dotnet.itags.org.Password Varchar( 16 )
as
SET NOCOUNT ON
declare @dotnet.itags.org.AccountID int
set @dotnet.itags.org.AccountID = -1
select @dotnet.itags.org.AccountID =
case
when Password = @dotnet.itags.org.Password and Active = 1 then AccountID
when Password = @dotnet.itags.org.Password then -2
else -3
end
from Account
where Username = @dotnet.itags.org.Username
return @dotnet.itags.org.AccountID
goA 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" <vnality@.msn.com> ha scritto nel messaggio
news:OVirIejtEHA.1332@.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
>
>
So is it good or bad to use SET NOCOUNT ON?
use my example sp for explanation. Thanks!
"Cirrosi" <CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote in message
news:66pdd.15666$1q2.1609@.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" <vnality@.msn.com> ha scritto nel messaggio
> news:OVirIejtEHA.1332@.TK2MSFTNGP10.phx.gbl...
>
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" <vnality@.msn.com> ha scritto nel messaggio
news:ue62yDotEHA.3860@.TK2MSFTNGP09.phx.gbl...
> So is it good or bad to use SET NOCOUNT ON?
> use my example sp for explanation. Thanks!
> "Cirrosi" <CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote in message
> news:66pdd.15666$1q2.1609@.tornado.fastwebnet.it...
>
Thank Again!
"Cirrosi" <CirrosiN_O-S_P_A-M@.fastwebnet.it> wrote in message
news:Dsqdd.15801$1q2.13733@.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" <vnality@.msn.com> ha scritto nel messaggio
> news:ue62yDotEHA.3860@.TK2MSFTNGP09.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" <vnality@.msn.com> wrote in message
news:OVirIejtEHA.1332@.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
>
>
Thanks Kikoz!
"Kikoz" <kikoz@.hotmail.com> wrote in message
news:eaPO9pqtEHA.444@.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" <vnality@.msn.com> wrote in message
> news:OVirIejtEHA.1332@.TK2MSFTNGP10.phx.gbl...
>
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 cou
nt 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 row
s 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 muc
h actual data, this can provide a significant performance boost because netw
ork traffic is greatly reduced.
The setting of SET NOCOUNT is set at execute or run time and not at parse ti
me.
"Leon" <vnality@.msn.com> ha scritto nel messaggio news:OVirIejtEHA.1332@.TK2MSFTNGP10.phx.gb
l...
> 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
>
>
>