views:

2070

answers:

4

Inspired by this question where there are differing views on SET NOCOUNT...

General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too.

MSDN says this can break a .net SQLDataAdapter.

Now, this means to me that the SQLDataAdapter is limited to utterly simply CRUD processing because it expects the "n rows affected" message to match. So, I can't use:

  • IF EXISTS to avoid duplicates (no rows affected message) Note: use with caution
  • WHERE NOT EXISTS (less rows then expected
  • Filter out trivial updates (eg no data actually changes)
  • Do any table access before (such as logging)
  • Hide complexity or denormlisation
  • etc

In the question marc_s (who knows his SQL stuff) says do not use it. This differs to what I think (and I regard myself as somewhat competent at SQL too).

It's possible I'm missing something (feel free to point out the obvious), but what do you folks out there think?

Note: it's been years since I saw this error because I don't use SQLDataAdapter nowadays.

Edit: More thoughts...

We have multiple clients: one may use a C# SQLDataAdaptor, another may use nHibernate from Java. These can be affected in different ways with SET NOCOUNT ON.

If you regard stored procs as methods, then it's bad form (anti-pattern) to assume some internal processing works a certain way for your own purposes.

Edit 2: a trigger breaking nHibernate question, where SET NOCOUNT ON can not be set

(and no, it's not a duplicate of this)

Edit 3: Yet more info, thanks to my MVP colleague

+4  A: 

I guess to some degree it's a DBA vs. developer issue.

As a dev mostly, I'd say don't use it unless you absolutely positively have to - because using it can break your ADO.NET code (as documented by Microsoft).

And I guess as a DBA, you'd be more on the other side - use it whenever possible unless you really must prevent it's usage.

Also, if your devs ever use the "RecordsAffected" being returned by ADO.NET's ExecuteNonQuery method call, you're in trouble if everyone uses SET NOCOUNT ON since in this case, ExecuteNonQuery will always return 0.

Also see Peter Bromberg's blog post and check out his position.

So it really boils down to who gets to set the standards :-)

Marc

marc_s
He's on about simple CRUD though: the data grid he mentions could use xml to send multiple rows to avoid round trips etc
gbn
I guess if you never use SqlDataAdapters, and you never check for and rely on the "records affected" number returned by ExecuteNonQuery (e.g. if you use something like Linq-to-SQL or NHibernate), then you probably don't have any problems using SET NOCOUNT ON in all stored procs.
marc_s
+11  A: 

Ok now I've done my research, here is the deal:

  • In TDS protocol SET NOCOUNT ON only saves 9-bytes per query, which doesn't seem that much. I used to think that "9 row(s) affected" was returned from server in plain text but it doesn't seem to be the case. It's in fact a small message packet called DONE_IN_PROC.

  • Microsoft actually encourages the use of SET NOCOUNT ON in Stored Procedures, as per the book "Improving .NET Application Performance and Scalability" (page 541). I would not base my design decisions upon SqlDataAdapter.

So I think you can stick with SET NOCOUNT ON's if the cost is less than switching to another technology. I would still consider abandoning SqlDataAdapter since you still don't know what kind of design quirk you'll encounter next.

ssg
Indeed. I've been using SET NOCOUNT ON forever, but marc_s pointed out the limitation of SQLDataAdapter in the other question.
gbn
Thanks. The bytes or size is not the issue to me, but the client has to process it. It's the SQLDataAdapter dependency that still astounds me though...
gbn
I don't have any benchmarks on client processing overhead, but I guess that processing 9 bytes wouldn't be that complicated or cumbersome.
ssg
(9 bytes per result set, that is)
ssg
The 9 bytes is treated as 2nd result set, which still has some overhead. Anyway, thanks for the answer.
gbn
Thanks for your answer. I'll accept this because of your investigations, which triggered more info and work from me. I disagree on the overhead though: it can matter as other answers show. Cheers, gbn
gbn
A: 

Regarding the triggers breaking NHibernate, I had that experience first-hand. Basically, when NH does an UPDATE it expects certain number of rows affected. By adding SET NOCOUNT ON to the triggers you get the number of rows back to what NH expected thereby fixing the issue. So yeah, I would definitely recommend turning it off for triggers if you use NH.

Regarding the usage in SPs, it's a matter of personal preference. I had always turned the row count off, but then again, there are no real strong arguments either way.

On a different note, you should really consider moving away from SP-based architecture, then you won't even have this question.

zvolkov
I disagree with moving away from stored procs. This would mean we have to have the same SQL in 2 different client code bases and trust our client coders. We're developer DBAs. And don't you mean "SET NOCOUNT *ON*"?
gbn
About SP vs no-SP, it's been done to death already... ;-)
gbn
+1  A: 

If you're saying you might have different clients as well, there are problems with classic ADO if SET NOCOUNT is not set ON.

One I experience regularly: if a stored procedure executes a number of statements (and thus a number of "xxx rows affected" messages are returned), ADO seems not to handle this and throws the error "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

So I generally advocate setting it ON unless there's a really really good reason not to. you may have found the really really good reason which I need to go and read into more.

Chris J