x

What does SET NOCOUNT ON do?

Whenever use SQL Server Management Studio and create a new stored procedure, it starts by filling the body of the procedure in with the following line:

SET NOCOUNT ON

Could someone explain to me what that does, when I should be leaving it in my stored procedure, and when I should be removing it?

more ▼

asked Oct 14, 2009 at 07:53 AM in Default

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

MSDN tells us that NOCOUNT:

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

It also tells us when its useful to use it:

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

So basically it can aid performance when you have a sProc that executes multiple statements, but that don't return a great deal of data.

more ▼

answered Oct 14, 2009 at 07:58 AM

Ben Adderson gravatar image

Ben Adderson ♦♦
361 5 7 8

(comments are locked)
10|1200 characters needed characters left

A while back, some ODBC libraries got entirely confused by DONE_IN_PROC messages, so it was actually needed in order to make client code function correctly. I don't believe that is the case any more with current generation libraries.

more ▼

answered Oct 14, 2009 at 08:32 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

SSRS (Report Services) likes you to use them in your Stored Procedures that support a report. Apparently too many messages coming back from the stored procedure can confuse the report processing.

more ▼

answered Oct 14, 2009 at 02:51 PM

jjerome gravatar image

jjerome
191

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x412
x126

asked: Oct 14, 2009 at 07:53 AM

Seen: 12631 times

Last Updated: Oct 14, 2009 at 07:56 AM