How NOCOUNT affects ADO.NET
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
at System.Data:
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.