10/05/2006

Note to Self: SET NOCOUNT ON, "NOT"!

I'm creating this web page for our Network Operations Center that allows them to test a specific mission - critical webservice that handles VOIP calls. It's basically a Web-based API for our internal Sip Proxy and Media servers, that I wrote. It allows any kind of device that can consume a standard WebService to use our VOIP Calling API.

So I've got like 30 different methods, and this page will iterate over every method, making a test call, and it populates a DataGridView with a new row that is colored green for success (with the results information) or red for failure, that includes any exception information, for every one of the methods in the test suite. So the guys in the NOC who need to have everything completely "idiot proof" can simply bring up this page, press the "Start" button, and watch it dynamically refresh as each method is called. All green, you are "OK". If you have a red row, you have a problem. If a method is supposed to do a database insert, and you get the wrong return value, you would also color it red, even though there was no exception.

So, I'm finishing method 25 of the 30 methods in the test page and it keeps coming up red. I know it works in the WebService itself because we've already tested it.

Finally I look at the stored proc it runs, and I see it:

SET NOCOUNT ON


Jeesh! This has been in ADO and ADO.NET since ancient times; when you make a SqlClient call such as ExecuteNonQuery, it returns an integer containing the number of records affected. Except -- when somebody put "SET NOCOUNT ON" in their stored procedure. You Database Folks, please don't do that unless there is a very good reason for it, OK? I couldn't find a single reason in this stored proc to have "SET NOCOUNT ON" - it was probably just force of habit for this person.

Rest my case.

N.B. I got a comment below indicating "surprise" that I should recommend this. For example, you may often see "optimization advice" that reads something like this:

"When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF, the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced."

The fact of the matter is, if you have a single row update on a single table, and you need to know if it succeeded, you are going to have network traffic whether you use an output parameter or whether you rely on ADO.NET's return value from having NOCOUNT off. The choice is yours to make, but the most important thing is that if the person who makes the call to the stored procedure isn't the same person who wrote it, and they haven't been properly informed or documented, you have a problem. It's a pretty safe bet to assume that the people who wrote ADO and now ADO.NET would not have put that feature there unless they expected lots of people to use it.