473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 2517
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: GM | last post by:
i use set nocount on inside the stored procedure at the beginning however the Number of Rows Counted/Affected shows up when I execute the stored procedure in query analyzer using execute...
3
by: Rithish Saralaya | last post by:
Is there a way to scroll a selected OPTION into view? I have a combination of text box and SELECT list. A user can key in text in the text box, and depending on the entry made, the respective...
3
by: Iain Hallam | last post by:
Hi. I've been using display:none on the style property of some <option> elements in my forms, which works fine with Mozilla - as expected it removes the option from my dropdown (although it...
3
by: Stewart | last post by:
Dear comp.lang.javascript, I have more than once wanted to manipulate the contents of select boxes dynamically, whilst the boxes contain <optgroup> tags. Manipulation of a select box containing...
1
by: Lyle Fairfield | last post by:
MS-SQL 2000, Access 2003, ADO 2.8 Please, consider the SQL string in the following code: Dim r As ADODB.Recordset Dim r2 As ADODB.Recordset Set r = CurrentProject.Connection.Execute( _ "SET...
3
by: Rob Meade | last post by:
Hi all, I'm trying to insert a record into a database via ASP, and then extract the id of the current row using @@IDENTITY... I've read the articles on ASPAQ and somewhere else regarding the...
0
by: praveen1983 | last post by:
How to find set nocount is on or off in the stored procedures?
3
by: anuragshrivastava64 | last post by:
Can anyone plz tell Set NoCount on equivalent in Oracle. I have used it only in SQL
3
by: Venturini | last post by:
I am trying to put together a web page where the customer makes choices of products and is then given a total. I am extremely new to Javascript and have managed to get as far as I have from web...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.