0

I have searched all afternoon and don't believe this is a duplicate to the other questions floating around like ExecuteNonQuery() and SET NOCOUNT ON.

I have also found an interesting blog about Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009) which does go into some interesting detail but still doesn't answer my question.

I have a database server where the admins have ticked the no count option for the server connections (In SSMS, right click the server in the Object Explorer, go to Properties, select the Connections page, under 'Default connection options', scroll down till you find the 'no count' selector).

Based on all the reading I've done, more and more admins may well be ticking the no count option to increase performance since the resulting rows will no longer be sent back to the client.

My question therefore mainly focuses on the SqlCommand.ExecuteNonQuery Method in C# since it relies on the fact that it:

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

But if SET NOCOUNT ON is enforced, then the result will always be -1. I've read about how people are recommending rather using select @rowcount = @@ROWCOUNT but this doesn't make up for the fact that you're losing functionality from the SqlCommand.ExecuteNonQuery method to the point that you might as well just start using SqlCommand.ExecuteScalar instead.

So my question is, for best practice, should we start setting SQL Servers to no count (or at the very least expect that's how they're going to start configuring them in the next couple of years) and then if so, should we be forcing SET NOCOUNT OFF or drop SqlCommand.ExecuteNonQuery in favour of select @rowcount = @@ROWCOUNT?

Storm
  • 1,848
  • 4
  • 20
  • 39
  • I'm pretty sure there's no such thing as a `no count` option. There's `SET NOCOUNT ON`, and there may be an option in third-party products that amounts to "automatically issue `SET NOCOUNT ON` when I check this" (Management Studio has such an option), but there's no SQL Server default (well, `OFF` is the default). Stored procedures very often start with `SET NOCOUNT ON`, but that's another matter. Where's this `no count` option the admins tick supposed to live? – Jeroen Mostert Sep 17 '18 at 15:39
  • To put it another way, if you set up an `SqlConnection` to the database (no special keywords), and you issue an `SqlCommand` with the text `DECLARE @T TABLE(ID INT); INSERT @T DEFAULT VALUES`, what does `ExecuteNonQuery` give you? It really ought to be `1` regardless of any SQL Server settings. In other words, `SET NOCOUNT` remains the choice of whatever's executing the queries. – Jeroen Mostert Sep 17 '18 at 15:55
  • Ah, hold on, I'm wrong! There *is* a way to get `NOCOUNT ON` to be the default, and it involves calling `sp_configure 'user options', 512`. (My version of Management Studio has no UI for this, but I suppose some versions might.) After this, you will indeed observe that `NOCOUNT` is in effect and all `ExecuteNonQuery` invocations give `-1` unless an explicit `SET NOCOUNT OFF` is issued first. Like any change from the factory defaults that changes SQL semantics, I'd very much recommend against doing this, regardless of purported gains -- developers won't expect it. – Jeroen Mostert Sep 17 '18 at 16:14
  • For "best practice", I'd say: if you *need* the count, use *either* a targeted `SET NOCOUNT OFF` or `@@ROWCOUNT`, as you see fit -- one is not much superior to the other. But conversely, on the server side, best practice is to leave the connection defaults alone so software that's written to the defaults (which is most of it) won't gratuitously break. I doubt anyone has thoroughly tested the actual performance gain and compared it to the cost of changing software, including your admins. Of all the things to optimize on a SQL Server, `NOCOUNT`'s pretty far down on the list. – Jeroen Mostert Sep 17 '18 at 16:31
  • @JeroenMostert In SSMS, right click the server in the Object Explorer, go to Properties, select the Connections page, under 'Default connection options', scroll down till you find the 'no count' selector. – Storm Sep 18 '18 at 06:20
  • 1
    Thanks, I spotted it. And now that I know how to change the defaults, I'll be extra content knowing I never will. On any server. Even though I really wouldn't mind, say, `XACT_ABORT` to be `ON` by default from a manageability perspective, the risk of breaking software is just too great and does not outweigh the potential benefits. You can be *damn* sure that, as long as an option continues to be supported in SQL Server, Microsoft won't touch these defaults either -- certainly not `NOCOUNT`. – Jeroen Mostert Sep 18 '18 at 13:11

1 Answers1

1

About a decade ago I started using SqlCop, which used to highlight "Procedures without SET NOCOUNT ON". Over time, I have evolved so I no longer use that, but I liked the concept, so I have a set of unit tests, which run metadata queries to find things like SqlCop used to highlight (and a whole lot more).

Each of my tests allows me to add exclusions to each rule, which allows me to document the justification for excluding it, in a comment.

My current test for that (which I don't have to hand) probably highlights Stored Procs which contain SELECT but not SET NOCOUNT ON. This way, INSERTs, UPDATEs and DELETEs are all left with the default behaviour, allowing my code to use the result of ExecuteNonQuery. Any fringe cases can be excluded from the rule.

This allows me to leave SQL server with the default behaviour, and my application's SPs choosing when to vary from Microsoft's default.

Notice that if someone modified that setting away from the default, then my code wouldn't work, because I haven't followed the advice at the very end of the RedGate article of "If you need to ensure that a query returns a rowcount message, you should specify it rather than assume the current setting.".

It's very sensible advice, in the sense that hindsight is a wonderful thing: you would only know that you needed to set it if you knew the default could be changed. A bit like you learn that scripting SPs as "IF EXISTS, DROP and CREATE" (so helpfully offered by SSMS) is the stupid way of doing it: better to just IF NOT EXISTS CREATE a placeholder, and then ALTER it to the actual definition (which avoids losing the metadata, like permissions).

Of course I could fix my SPs, and add a new or changed unit test to ensure that I always set the NOCOUNT option one way or the other. But the point I'm making is that it wouldn't surprise me if setting that behaviour different from the default might also break any third-party products you install on that server. (Maybe that doesn't matter in your scenario.)

Whichever way you decide to go, I just felt that this technique of having unit tests to enforce your decided approach is invaluable. Checking that it is always set one way or the other by each SP would seem to be sensible.

Richardissimo
  • 5,596
  • 2
  • 18
  • 36
  • Thanks for this, my main question still remains about what's the future of SqlCommand.ExecuteNonQuery. If we migrate to @@ROWCOUNT then really it becomes obsolete. I guess I'm hoping for an answer closer to the guys who actually make the decisions vs our opinions. If anything, SqlCommand.ExecuteNonQuery should be altered to make use of @@ROWCOUNT instead? – Storm Sep 18 '18 at 07:27
  • I'm afraid, *"what's the future of"* is going to be opinion-based. In fact, on reflection, the question itself is opinion-based, which is [vote-to-close](https://stackoverflow.com/help/closed-questions)-worthy. The default value of NOCOUNT currently has a particular value. Changing it would be a breaking change (which is the point I'm trying to make here). So my *opinion* is that Microsoft won't be changing that default any time soon. – Richardissimo Sep 18 '18 at 07:42
  • I hear what you're saying and I agree, however I've also seen responses from insiders who know exactly what MS is doing (like on the C# language team) where I've often seen info about what they're planning or why they decided on something. My question is therefore aimed at getting someone closer to subject to enlighten us. Hope that makes sense. – Storm Sep 21 '18 at 07:54
  • @Storm: `My question is therefore aimed at getting someone closer to subject to enlighten us` ;then why post it here? Seeing this answer, knowing this upfront would have saved somebody a lot of time. Nevertheless, this answer makes perfect sense: don't break the default unless you have to. – Stefan Jul 12 '19 at 21:53
  • @Stefan, where should I have then posted? – Storm Jul 15 '19 at 04:50