CODEWITHSUNDEEP
sql-servernocount
Krishna Kumar
Krishna Kumar

Reputation: 4884

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries?

What are the advantages and disadvantages of turning NOCOUNT off in SQL server queries? ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­

Upvotes: 15

Views: 14682

Answers (7)

user2304723
user2304723

Reputation: 1

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.

Upvotes: 0

Murugan
Murugan

Reputation: 111

SET NOCOUNT ON is an oneline Statement, Sql server sends message back to client.this is performed for Every Process(ie .. select,insert,update,delete).if you avoid this message we can improve overall performance for our Database and also reduce network traffic

For EX:

declare @a table(id int)

set nocount on

insert @a select 1 union select 2

set nocount off

Upvotes: 0

jerhinesmith
jerhinesmith

Reputation: 15500

I personally like to turn NOCOUNT on for queries that get run in an manual fashion and use a lot of Print statements to output debugging messages. In this way, your output would look less like:

Updating usernames
(287 rows updated)

Done
Updating passwords
(287 rows updated)

Done
Doing the next thing
(1127 rows updated)

Done

And more like

Updating usernames
Done

Updating passwords
Done

Doing the next thing
Done

Depending on the sensitivity of what you're updating, sometimes it is helpful to include the counts; however, for complex scripts with a lot of output I usually like to leave them out.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135161

I always have it set to ON for the reasons above, but if you have more than 1 result set in your proc it could mess up client code

Upvotes: 1

public static
public static

Reputation: 13000

It simply stops the message that shows the # of rows effected for being sent/displayed, which provides a performance benefit, especially if you have many statements that will return the message. It improves performance since less data is being sent over the network (between the sql server and front end).

More at BOL: SET NOCOUNT

Upvotes: 4

Vaibhav
Vaibhav

Reputation: 11436

And it's not just the network traffic that is reduced. There is a boost internal to SQL Server because the execution plan can be optimized due to reduction of an extra query to figure out how many rows were affected.

Upvotes: 8

Ryan Farley
Ryan Farley

Reputation: 11431

From SQL BOL:

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, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

See http://msdn.microsoft.com/en-us/library/ms189837.aspx for more details.
Also, this article on SQLServerCentral is great on this subject:
Performance Effects of NOCOUNT

Upvotes: 19

Related Questions