This is from SQL server 2000 Books Online about NOCOUNT.
In serveral SQL server programming books I have, they all recommend
setting NOCOUNT ON. One book I have mentioned the SET NOCOUNT ON can
cause problems with Access, so you should limit its use to SQL server.
Tom Nunamaker
==============================================
SET NOCOUNT
Stops the message indicating the number of rows affected by a
Transact-SQL statement from being returned as part of the results.
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 MicrosoftR 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.
Permissions
SET NOCOUNT permissions default to all users.
-----Original Message-----
From: Lawrence B. Afrin, M.D. [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 18, 2002 10:19 AM
To: CF-Talk
Subject: NOCOUNT question
Howdy --
I have seen some in this forum suggest implicitly via their code
examples that SET NOCOUNT ON...SET NOCOUNT OFF should bracket any SQL
command(s) submitted via CF to SQL Server. I realize that for "routine"
operations these statements are not necessary, but I was wondering
whether there are special situations where they might be helpful or even
necessary. I searched the CF support forums, the SQL Server docs and
Microsoft support KB, and even Google, but aside from a few comments to
the effect that this approach might provide a very slight performance
advantage in high-traffic environments, I really couldn't find any other
reason to explain why some CF coders routinely use this approach.
Enlightening comments, anyone?
-- Larry Afrin
Med. Univ. of S.C.
[EMAIL PROTECTED]
______________________________________________________________________
Get Your Own Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
