<p>What are the advantages and disadvantages of turning <strong><code>NOCOUNT</code></strong> off in SQL server queries?
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­</p>

[{'answer_id': 26555, 'author': 'Ryan Farley', 'author_id': 1627, 'author_profile': 'https://Stackoverflow.com/users/1627', 'pm_score': 5, 'selected': True, 'text': '<p>From SQL BOL:  </p>\n\n<blockquote>\n  <p>SET NOCOUNT ON prevents the sending of\n  DONE_IN_PROC messages to the client\n  for each statement in a stored\n  procedure. For stored procedures that\n  contain several statements that do not\n  return much actual data, <strong>setting SET\n  NOCOUNT to ON can provide a\n  significant performance boost</strong>, because\n  network traffic is greatly reduced.</p>\n</blockquote>\n\n<p>See <a href="http://msdn.microsoft.com/en-us/library/ms189837.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms189837.aspx</a> for more details.<br>\nAlso, this article on SQLServerCentral is great on this subject:<br>\n<a href="http://www.sqlservercentral.com/articles/Performance+Tuning/2751/" rel="noreferrer">Performance Effects of NOCOUNT</a></p>\n'}, {'answer_id': 26561, 'author': 'Vaibhav', 'author_id': 380, 'author_profile': 'https://Stackoverflow.com/users/380', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 26562, 'author': 'public static', 'author_id': 1368, 'author_profile': 'https://Stackoverflow.com/users/1368', 'pm_score': 2, 'selected': False, 'text': '<p>It simply stops the message that shows the # of rows effected for being sent/displayed, which provides a <strong>performance</strong> 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).</p>\n\n<p>More at BOL: <a href="http://msdn.microsoft.com/en-us/library/ms189837.aspx" rel="nofollow noreferrer">SET NOCOUNT</a></p>\n'}, {'answer_id': 26566, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 1, 'selected': False, 'text': '<p>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</p>\n'}, {'answer_id': 26588, 'author': 'jerhinesmith', 'author_id': 1108, 'author_profile': 'https://Stackoverflow.com/users/1108', 'pm_score': 0, 'selected': False, 'text': "<p>I personally like to turn <strong>NOCOUNT</strong> on for queries that get run in an manual fashion and use a lot of <code>Print</code> statements to output debugging messages.  In this way, your output would look less like:</p>\n\n<pre>\nUpdating usernames\n(287 rows updated)\n\nDone\nUpdating passwords\n(287 rows updated)\n\nDone\nDoing the next thing\n(1127 rows updated)\n\nDone\n</pre>\n\n<p>And more like</p>\n\n<pre>\nUpdating usernames\nDone\n\nUpdating passwords\nDone\n\nDoing the next thing\nDone\n</pre>\n\n<p>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.</p>\n"}, {'answer_id': 13100767, 'author': 'Murugan', 'author_id': 1703511, 'author_profile': 'https://Stackoverflow.com/users/1703511', 'pm_score': 0, 'selected': False, 'text': '<p>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</p>\n\n<p>For EX:</p>\n\n<p>declare @a table(id int)</p>\n\n<p>set nocount  on</p>\n\n<p>insert @a select 1 union select 2</p>\n\n<p>set nocount off</p>\n'}, {'answer_id': 22954206, 'author': 'user2304723', 'author_id': 2304723, 'author_profile': 'https://Stackoverflow.com/users/2304723', 'pm_score': 0, 'selected': False, 'text': '<p>Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results.</p>\n'}]
['https://Stackoverflow.com/questions/26552', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2141/']