nocount returns rows affected

I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
affected" when I execute the sproc. It seems to happen sporadically too.
Sometimes if I exit QA and go back in, it will execute w/o returning row
counts - other times I continue to get the number of rows affected returned.
Is this just voodoo with my system, or is there an explainable reason why
this happens?
Thanks, Andresql2k: check to see if any SET STATISTICS options are ON. [Tools \
Options \ Connection Properties]
If these affect any rows for their logging, the rowcounts are returned,
regardless of the set nocount setting.
Andre wrote:
> I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
> affected" when I execute the sproc. It seems to happen sporadically too.
> Sometimes if I exit QA and go back in, it will execute w/o returning row
> counts - other times I continue to get the number of rows affected returne
d.
> Is this just voodoo with my system, or is there an explainable reason why
> this happens?
> Thanks, Andre
>|||In addition to what Trey said, what kind of statements are you executing?
SELECTS, UPDATES, etc? Any chance an object is being called that has SET
NOCOUNT OFF (I would rather doubt it, but never hurts to ask)
Also, does this anomoly happen on any of your procs, or just a select few?
Can you post the code, if it is just a few?
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Andre" <no@.spam.com> wrote in message
news:uby%239yLEGHA.2704@.TK2MSFTNGP15.phx.gbl...
>I have several sprocs that have SET NOCOUNT ON, yet still return "n row(s)
> affected" when I execute the sproc. It seems to happen sporadically too.
> Sometimes if I exit QA and go back in, it will execute w/o returning row
> counts - other times I continue to get the number of rows affected
> returned.
> Is this just voodoo with my system, or is there an explainable reason why
> this happens?
> Thanks, Andre
>|||It happens in all of my sprocs, but like I said - it's sporadic. For
instance, yesterday when I posted this issue, a sproc was returning rows
affected. I exited QA, went back in, and the same sproc worked w/o
returning rows affected. The set nocount on stmt has been in the sproc
forever; it wasn't just added yesterday. That's why I wonder if I have some
sort of voodoo/gremlins in my box. :)
Andre|||I hate to agree with the gremlins/voodoo diagnosis, but it does sound like
something wierd going on. Do you have this problem on any other box? Like
try it out on OSQL/SQLCMD, or on the QA on the server also (particularly if
you can do it at the same time you are having issues on your development
box)
This way you can isolate it down to the box or server. Wish there was
better advice, but you could try calling GhostBusters :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Andre" <no@.spam.com> wrote in message
news:O7U2V5TEGHA.2912@.tk2msftngp13.phx.gbl...
> It happens in all of my sprocs, but like I said - it's sporadic. For
> instance, yesterday when I posted this issue, a sproc was returning rows
> affected. I exited QA, went back in, and the same sproc worked w/o
> returning rows affected. The set nocount on stmt has been in the sproc
> forever; it wasn't just added yesterday. That's why I wonder if I have
> some
> sort of voodoo/gremlins in my box. :)
> Andre
>

nocount on problem

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 sprocName. I also tried to
add the

SET NOCOUNT ON at the beginning of the procedure and it still shows the
number of rows affected.
if i set

set nocount on
exec sprocName then the result set does not show the number of rows
affected.

Any idea why this happens? I know that NOCOUNT is set on runtime not
parse time.

Thanks!"GM" <gentian.metaj@.trustasc.com> wrote in message
news:1104772581.850237.15630@.z14g2000cwz.googlegro ups.com...
>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 sprocName. I also tried to
> add the
> SET NOCOUNT ON at the beginning of the procedure and it still shows the
> number of rows affected.
> if i set
> set nocount on
> exec sprocName then the result set does not show the number of rows
> affected.
> Any idea why this happens? I know that NOCOUNT is set on runtime not
> parse time.
> Thanks!

I have no idea - the following trivial example works for me, ie. it does not
display the rows affected (in Query Analyzer), using MSSQL 2000 build
8.00.760:

create proc foo
as
begin
set nocount on
select * from master.dbo.sysdatabases
end
go

exec foo
go

If this doesn't help, you might want to post a minimal code example for
Query Analyzer which shows the problem you have, along with details of your
MSSQL version.

Simon|||Thanks Simon. NoCount seems to work OK. The issue was that i was using
IMCEDA SpeedSQL and i'm assuming somehow they do something weird
(Probably get @.@.rowCount or something) but when i ran it on regular QA
it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
SQLExpress) must put some extra code under their interface.

While i was reading Books online it says "The setting of SET NOCOUNT is
set at execute or run time and not at parse time." What are the
implications of that statement?

Thanks

Gent|||"GM" <gentian.metaj@.trustasc.com> wrote in message
news:1104779732.275600.51970@.c13g2000cwb.googlegro ups.com...
> Thanks Simon. NoCount seems to work OK. The issue was that i was using
> IMCEDA SpeedSQL and i'm assuming somehow they do something weird
> (Probably get @.@.rowCount or something) but when i ran it on regular QA
> it seemed to work. Apparently Imceda SpeedSQL (Formerly known and
> SQLExpress) must put some extra code under their interface.
> While i was reading Books online it says "The setting of SET NOCOUNT is
> set at execute or run time and not at parse time." What are the
> implications of that statement?
> Thanks
> Gent

As I understand it, a 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 (because
of branching logic or error handling). A run-time option is set only when
that part of code really does execute. See "SET Options" in Books Online for
more details.

By the way, if you want to check up on exactly what SpeedSQL is doing, you
can use Profiler to trace all TSQL sent from it to the server.

Simon

Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
Paperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>
|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
7780
"Bojidar Alexandrov" wrote:

> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>
|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on, I had 2000...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...[vbcol=seagreen]
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
using[vbcol=seagreen]
development[vbcol=seagreen]
for
>
|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development[vbcol=seagreen]
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
for[vbcol=seagreen]

nocount

Hi,
Is it true that setting nocount on in a trigger is almost
a good practice? When it is not true?
Many thanks,
Oskar
Oscar
When SET NOCOUNT ON SQL Server does not inform users about rows were
affected.
It is good practice to set it up at the beginning of stored procedures but i
have not seen this command within triggers. It's meaningless.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
> Hi,
> Is it true that setting nocount on in a trigger is almost
> a good practice? When it is not true?
> --
> Many thanks,
> Oskar
>
|||On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:

>Oscar
>When SET NOCOUNT ON SQL Server does not inform users about rows were
>affected.
>It is good practice to set it up at the beginning of stored procedures but i
>have not seen this command within triggers. It's meaningless.
Hi Uri,
This is not correct. Run the following code with and wothout the set
nocount on statement in the trigger to see the difference.
set nocount off
go
create table test (a int not null)
go
create table test2 (now datetime not null primary key default(getdate()),
num int not null)
go
create trigger testtrig on test after insert
as
--set nocount on
declare @.num int
select @.num = count(*)
from inserted
insert into test2(num)
select @.num
update test set a = a
go
insert test
select 1 union all select 2
go
select * from test2
go
drop table test2, test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Uri,
But what about this remark in books on-line:
"It is recommended that a trigger not return any results.
This is because special handling for these returned
results must be written into every application in which
modifications to the trigger table are allowed. To prevent
any results from being returned from a trigger, do not
include either SELECT statements or variable assignments
in the definition of the trigger. If variable assignment
must occur in a trigger, use a SET NOCOUNT statement at
the beginning of the trigger to eliminate the return of
any result sets."
I do assignments and selects in my triggers. I wonder if I
set nocount on in every trigger, will it have any
noticeable, positive effect.
Thanks,
Oskar

>--Original Message--
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users
about rows were
>affected.
>It is good practice to set it up at the beginning of
stored procedures but i
>have not seen this command within triggers. It's
meaningless.
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
almost
>
>.
>
|||Hi,Hugo
> declare @.num int
> select @.num = count(*)
What was a purpose to declare this variable and not using it?
If if does affect the perfomance (some big logic inside the trigger) so I
would not use trigger at all.
But now I have just finished read this article in the BOL and I will have
to review my triggers again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2jb2m0dpt5k6g9vjr8c0hkmftaf2amhumd@.4ax.com... [vbcol=seagreen]
> On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
but i
> Hi Uri,
> This is not correct. Run the following code with and wothout the set
> nocount on statement in the trigger to see the difference.
> set nocount off
> go
> create table test (a int not null)
> go
> create table test2 (now datetime not null primary key default(getdate()),
> num int not null)
> go
> create trigger testtrig on test after insert
> as
> --set nocount on
> declare @.num int
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num
> update test set a = a
> go
> insert test
> select 1 union all select 2
> go
> select * from test2
> go
> drop table test2, test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:

>Hi,Hugo
>What was a purpose to declare this variable and not using it?
Hi Uri,
It is used in the insert-statement following the select where it is set.
Of course, this trigger could have been written otherwise. The update
statement doesn't do anything usseful either. But I wanted a quick example
to show something and I didn't want to spend the time to make up something
more realistic.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi, Hugo
select @.num = count(*)
from inserted
insert into test2(num)
I see now , you just missed a @.,
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:b7i2m0pgmvg03aus9duhbdvf65df3mlejm@.4ax.com...
> On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
>
> Hi Uri,
> It is used in the insert-statement following the select where it is set.
> Of course, this trigger could have been written otherwise. The update
> statement doesn't do anything usseful either. But I wanted a quick example
> to show something and I didn't want to spend the time to make up something
> more realistic.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:

>Hi, Hugo
>select @.num = count(*)
>from inserted
>insert into test2(num)
>I see now , you just missed a @.,
Hi Uri,
Did I? Or did you miss a whole line?
select @.num = count(*)
from inserted
insert into test2(num)
select @.num -- Here, it is used!!!!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi ,Hugo
You said
>It is used in the insert-statement following the >select where it is set.
Now you are saying
> select @.num -- Here, it is used!!!!
Or it was just time to go sleep
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rmi3m0tsge4jufmj7cukaqv72r6km8kjq0@.4ax.com...
> On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
>
> Hi Uri,
> Did I? Or did you miss a whole line?
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num -- Here, it is used!!!!
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspxPaperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
--
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
--
7780
"Bojidar Alexandrov" wrote:

> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on
, I had 2000...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...[vbcol=seagreen]
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by usi
ng
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our developme
nt
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
using[vbcol=seagreen]
development[vbcol=seagreen]
for[vbcol=seagreen]
>|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development[vbcol=seagreen]
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>
for[vbcol=seagreen]

Nocount

Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
SELECT...
--
Please, check my theSpoke:
http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspxPaperback Writer wrote:
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only
> for SELECT...
It good practice to have a SET NOCOUNT ON at the top of every stored
procedure and also to execute upon initial connection if there is a
chance that ad-hoc SQL will be sent from the application to the server.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Its not needed really.
Bojidar Alexandrov
"Paperback Writer" <newsgroupms@.gmail.com> wrote in message
news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> SELECT...
> --
> Please, check my theSpoke:
> http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>|||hahahahahahahahah
Classic.
In our environment the single biggest performance gain was obtained by using
this set option but don't take anybodys word for it. Test for yourself.
When push came to shove and I had to convice the managers of our development
team I used this simple example.
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount on
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
dbcc dropcleanbuffers
go
dbcc freeproccache
go
set nocount off
go
DECLARE @.start_time DATETIME
SELECT @.start_time = GETDATE()
select * from BIG_SALES2
SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
GO
results from first select
Elapsed Time, Msec
--
7516
results from second select
(99999 row(s) affected)
Elapsed Time, Msec
--
7780
"Bojidar Alexandrov" wrote:
> Its not needed really.
> Bojidar Alexandrov
> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
> > SELECT...
> >
> > --
> > Please, check my theSpoke:
> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >
> >
>|||I had a loop with
BEGIN TRAN
UPDATE
COMMIT
And counter transaction per second. With the setting off, I has 800, with on, I had 2000...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
>> Its not needed really.
>> Bojidar Alexandrov
>> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
>> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
>> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only for
>> > SELECT...
>> >
>> > --
>> > Please, check my theSpoke:
>> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
>> >
>> >
>>|||Tibor, the thing with update (or delete statements) is very clear that it
takes too much time to transfer these (x rows affected) as another
recordset, but original question was for Select statements only. When there
are staments returning stats like update and delete you are very right.
Im not sure that the example from Joe is right, so will test myself.
Bojidar Alexandrov
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23%23fBUhFaFHA.616@.TK2MSFTNGP12.phx.gbl...
> I had a loop with
> BEGIN TRAN
> UPDATE
> COMMIT
> And counter transaction per second. With the setting off, I has 800, with
on, I had 2000...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> > hahahahahahahahah
> > Classic.
> > In our environment the single biggest performance gain was obtained by
using
> > this set option but don't take anybodys word for it. Test for yourself.
> > When push came to shove and I had to convice the managers of our
development
> > team I used this simple example.
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount on
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> > dbcc dropcleanbuffers
> > go
> > dbcc freeproccache
> > go
> > set nocount off
> > go
> > DECLARE @.start_time DATETIME
> > SELECT @.start_time = GETDATE()
> > select * from BIG_SALES2
> > SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> > GO
> >
> > results from first select
> >
> > Elapsed Time, Msec
> > --
> > 7516
> >
> > results from second select
> > (99999 row(s) affected)
> > Elapsed Time, Msec
> > --
> > 7780
> >
> >
> > "Bojidar Alexandrov" wrote:
> >
> >> Its not needed really.
> >>
> >> Bojidar Alexandrov
> >>
> >> "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> >> news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> >> > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> >> > SELECT...
> >> >
> >> > --
> >> > Please, check my theSpoke:
> >> > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> >> >
> >> >
> >>
> >>
>|||I've just tested out and do not think you are right. Mine results from 10
trys on table with about 100k rows are on the average same.
Bojidar Alexandrov
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FFD0E059-E183-46A2-90F6-03902FD63FD8@.microsoft.com...
> hahahahahahahahah
> Classic.
> In our environment the single biggest performance gain was obtained by
using
> this set option but don't take anybodys word for it. Test for yourself.
> When push came to shove and I had to convice the managers of our
development
> team I used this simple example.
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount on
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> dbcc dropcleanbuffers
> go
> dbcc freeproccache
> go
> set nocount off
> go
> DECLARE @.start_time DATETIME
> SELECT @.start_time = GETDATE()
> select * from BIG_SALES2
> SELECT 'Elapsed Time, Msec' = DATEDIFF( Ms, @.start_time, GETDATE() )
> GO
> results from first select
> Elapsed Time, Msec
> --
> 7516
> results from second select
> (99999 row(s) affected)
> Elapsed Time, Msec
> --
> 7780
>
> "Bojidar Alexandrov" wrote:
> > Its not needed really.
> >
> > Bojidar Alexandrov
> >
> > "Paperback Writer" <newsgroupms@.gmail.com> wrote in message
> > news:uDgS9M3ZFHA.3912@.TK2MSFTNGP10.phx.gbl...
> > > Hi, I'd like to know if i need to set Nocount On/Off in a proc only
for
> > > SELECT...
> > >
> > > --
> > > Please, check my theSpoke:
> > > http://www.thespoke.net/MyBlog/dgroh/MyBlog.aspx
> > >
> > >
> >
> >

nocount

Hi,
Is it true that setting nocount on in a trigger is almost
a good practice? When it is not true?
--
Many thanks,
OskarOscar
When SET NOCOUNT ON SQL Server does not inform users about rows were
affected.
It is good practice to set it up at the beginning of stored procedures but i
have not seen this command within triggers. It's meaningless.
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
> Hi,
> Is it true that setting nocount on in a trigger is almost
> a good practice? When it is not true?
> --
> Many thanks,
> Oskar
>|||On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users about rows were
>affected.
>It is good practice to set it up at the beginning of stored procedures but i
>have not seen this command within triggers. It's meaningless.
Hi Uri,
This is not correct. Run the following code with and wothout the set
nocount on statement in the trigger to see the difference.
set nocount off
go
create table test (a int not null)
go
create table test2 (now datetime not null primary key default(getdate()),
num int not null)
go
create trigger testtrig on test after insert
as
--set nocount on
declare @.num int
select @.num = count(*)
from inserted
insert into test2(num)
select @.num
update test set a = a
go
insert test
select 1 union all select 2
go
select * from test2
go
drop table test2, test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Uri,
But what about this remark in books on-line:
"It is recommended that a trigger not return any results.
This is because special handling for these returned
results must be written into every application in which
modifications to the trigger table are allowed. To prevent
any results from being returned from a trigger, do not
include either SELECT statements or variable assignments
in the definition of the trigger. If variable assignment
must occur in a trigger, use a SET NOCOUNT statement at
the beginning of the trigger to eliminate the return of
any result sets."
I do assignments and selects in my triggers. I wonder if I
set nocount on in every trigger, will it have any
noticeable, positive effect.
--
Thanks,
Oskar
>--Original Message--
>Oscar
>When SET NOCOUNT ON SQL Server does not inform users
about rows were
>affected.
>It is good practice to set it up at the beginning of
stored procedures but i
>have not seen this command within triggers. It's
meaningless.
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:3e7601c4a9fc$70b30650$a601280a@.phx.gbl...
>> Hi,
>> Is it true that setting nocount on in a trigger is
almost
>> a good practice? When it is not true?
>> --
>> Many thanks,
>> Oskar
>
>.
>|||Hi,Hugo
> declare @.num int
> select @.num = count(*)
What was a purpose to declare this variable and not using it?
If if does affect the perfomance (some big logic inside the trigger) so I
would not use trigger at all.
But now I have just finished read this article in the BOL and I will have
to review my triggers again.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:2jb2m0dpt5k6g9vjr8c0hkmftaf2amhumd@.4ax.com...
> On Mon, 4 Oct 2004 12:41:56 +0200, Uri Dimant wrote:
> >Oscar
> >When SET NOCOUNT ON SQL Server does not inform users about rows were
> >affected.
> >It is good practice to set it up at the beginning of stored procedures
but i
> >have not seen this command within triggers. It's meaningless.
> Hi Uri,
> This is not correct. Run the following code with and wothout the set
> nocount on statement in the trigger to see the difference.
> set nocount off
> go
> create table test (a int not null)
> go
> create table test2 (now datetime not null primary key default(getdate()),
> num int not null)
> go
> create trigger testtrig on test after insert
> as
> --set nocount on
> declare @.num int
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num
> update test set a = a
> go
> insert test
> select 1 union all select 2
> go
> select * from test2
> go
> drop table test2, test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
>Hi,Hugo
>> declare @.num int
>> select @.num = count(*)
>What was a purpose to declare this variable and not using it?
Hi Uri,
It is used in the insert-statement following the select where it is set.
Of course, this trigger could have been written otherwise. The update
statement doesn't do anything usseful either. But I wanted a quick example
to show something and I didn't want to spend the time to make up something
more realistic.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi, Hugo
select @.num = count(*)
from inserted
insert into test2(num)
I see now , you just missed a @., :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:b7i2m0pgmvg03aus9duhbdvf65df3mlejm@.4ax.com...
> On Mon, 4 Oct 2004 13:35:42 +0200, Uri Dimant wrote:
> >Hi,Hugo
> >> declare @.num int
> >> select @.num = count(*)
> >What was a purpose to declare this variable and not using it?
> Hi Uri,
> It is used in the insert-statement following the select where it is set.
> Of course, this trigger could have been written otherwise. The update
> statement doesn't do anything usseful either. But I wanted a quick example
> to show something and I didn't want to spend the time to make up something
> more realistic.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
>Hi, Hugo
>select @.num = count(*)
>from inserted
>insert into test2(num)
>I see now , you just missed a @., :)
Hi Uri,
Did I? Or did you miss a whole line?
select @.num = count(*)
from inserted
insert into test2(num)
select @.num -- Here, it is used!!!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi ,Hugo
You said
>It is used in the insert-statement following the >select where it is set.
Now you are saying
> select @.num -- Here, it is used!!!!
Or it was just time to go sleep
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rmi3m0tsge4jufmj7cukaqv72r6km8kjq0@.4ax.com...
> On Mon, 4 Oct 2004 15:12:01 +0200, Uri Dimant wrote:
> >Hi, Hugo
> >select @.num = count(*)
> >from inserted
> >insert into test2(num)
> >
> >I see now , you just missed a @., :)
> Hi Uri,
> Did I? Or did you miss a whole line?
> select @.num = count(*)
> from inserted
> insert into test2(num)
> select @.num -- Here, it is used!!!!
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)