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.
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.
I'm very surprised you're actually recommending against setting nocount on by default. Starting off my career as a dba and switching to software development and being an architect I recomment setting nocount on by default and only setting it off for specific scenarios. I'm sure you can find all the various reasons why to set nocount on by default (Such as limiting chattiness between client/server) but I think the most important one is that if you have more than one statement in the stored procedure that can affect rows, with nocount off, you'll get counts for each one, and ExecuteNonQuery will take whatever the last one was, resulting in erroneous data. I feel it is much much better to create an output param instead of relying on count results being returned from the db. That way, your code expresses explicitly that yes, you want the actual count back and are using it for some specific reason. That way even if the stored proc is changed later on in the future, the next dev will know your intentions by looking at the code, instead of having to think to himself "Hmm...no count wasn't set to off, I wonder why...Now I have go look to see what uses this proc and determine if it was just a goof by someone that forgot to turn it off so that I don't break any existing code"
ReplyDeleteJust my 2 cents...
What you are describing is the "Very good reason" scenario which I did specifically address in my post.
ReplyDeleteIn that case, yes, we would probably have had output parameters. But in the majority of the cases where there is a simple insert or update on a single table, that's overkill - you should be able to simply rely on the ADO.NET return value.
There's also the disconnect to think about - very often the person who writes the stored proc is NOT the same person as the developer who makes a call to it.
My SP's almost always look like
ReplyDeleteSET NOCOUNT ON
...
-- do something
-- no data is sent in here
...
SET NOCOUNT OFF
-- send the results
SELECT
...
This gives you the benefit of not sending unneed row counts to the client, but still sending the row count for the final result.
Why would you not rely upon a try catch block and RAISEERROR to determine your failure?
ReplyDeleteIf your update statement did not throw an exception and updated zero rows, then wouldn't it be indicative of a larger problem in your solution?
@Elan error handling and deciding whether to return the rowcount of an operation are two very separate and distinct things.
ReplyDelete