A co-worker recommended we include "SET NOCOUNT ON" in all stored procedures, since it reduces the network traffic for queries. I did a bit of digging; here's my take on it:
It's probably a good general practice, but the network traffic savings would be pretty minimal in most cases. This would be more important in a case where a very high volume of SQL statements with small result sets were being run, because the rowcount information would comprise a more significant portion of the network traffic. NOCOUNT doesn't make any difference on the database load, since @@ROWCOUNT is still populated regardless of the NOCOUNT setting.
UPDATE: NOCOUNT can make a significant performance difference if you are executing a large number of SQL statements, such as in the example Scott Whigham points out in his comment or the example in this article. That's because each executed SQL statement reports completion, which can cause a lot of network traffic just to report "1 row inserted.1 row inserted. 1 row inserted." That's a very valid point, and while I do stand corrected I'd point out that it's a bit of a contrived countercase. I definitely wouldn't recommend looping inserts if there's any way you can perform a bulk or batch insert. I've recently trimmed SQL execution time for a single proc from 8 hours a few minutes by converting from looping inserts to an INSERT...SELECT statement (no, I didn't write the original proc...). The point is well taken, though - if the SQL call will execute a significant number of operations, by all means SET NOCOUNT ON.
Previously, in Classic ASP and ADO, NOCOUNT was most commonly a factor if you were checking RecordsAffected on Command.Execute(). SqlDataReader does have a RecordsAffected property, but it's rarely used since it's not populated until the datareader has iterated all the rows and the datareader is closed. There are some possible implications if you're using DataAdapters to submit changes to the database, since it uses the rows affected result to determine if the update succeeded. Probably the easiest way to check for that case is to search the codebase for SqlDataAdapter and see if the Update() method is being called.
In our case, we determined that it would be good to use on a going forward basis, but the minimal savings in network traffic didn't justify the risk of modifying existing procedures.
Tech notes if you're interested in tracing how RecordsAffected is set and
used by pointing Reflector
System.Data.SqlClient.TdsParser.ProcessDone() sets RecordsAffected.
System.Data.Common.DbDataAdapter.UpdateRow() uses reader.RecordsAffected in determining whether to call AcceptChanges and ApplyToDataRow.
Please let me know if I got any of this wrong or you disagree with my conclusions.