SET NOCOUNT ON usage

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

Should we use SET NOCOUNT ON for SQL Server? If not, why not?

What it does Edit 6, on 22 Jul 2011

It suppresses the “xx rows affected” message after any DML. This is a resultset and when sent, the client must process it. It’s tiny, but measurable (see answers below)

For triggers etc, the client will receive multiple “xx rows affected” and this causes all manner of errors for some ORMs, MS Access, JPA etc (see edits below)

Background:

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.

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.

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.

EDIT: @racingsnail pointed out that the network roundtrip delay is a bigger performance killer than the packet size. He has a point but a second network packet wouldn’t cause the same delay as the roundtrip latency because the packets would be sent in tandem and would not require acknowledgement. So it may cause far less delay than the actual network roundtrip latency.

It took me a lot of digging to find real benchmark figures around NOCOUNT, so I figured I’d share a quick summary.

If your stored procedure uses a cursor to perform a lot of very quick operations with no returned results, having NOCOUNT OFF can take roughly 10 times as long as having it ON. 1 This is the worst-case scenario.

If your stored procedure only performs a single quick operation with no returned results, setting NOCOUNT ON will yield around a 3% performance boost. 2 This would be consistent with a typical insert or update procedure.

If your stored procedure returns results (i.e. you SELECT something), the performance difference will diminish proportionately with the size of the result set.

Questions:

Answers:

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.

SET NOCOUNT ON improves stored procedure (SP) performance.

Syntax: SET NOCOUNT { ON | OFF }

Example of SET NOCOUNT ON:

Example of SET NOCOUNT OFF:

Questions:

Answers:

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.

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.

Questions:

Answers:

At the risk of making things more complicated, I encourage a slightly different rule to all those I see above:

Always set NOCOUNT ON at the top of a proc, before you do any work in the proc, but also always SET NOCOUNT OFF again, before returning any recordsets from the stored proc.

So “generally keep nocount on, except when you are actually returning a resultset”. I don’t know any ways that this can break any client code, it means client code never needs to know anything about the proc internals, and it isn’t particularly onerous.

Questions:

Answers:

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.

Questions:

Answers:

I don’t know how to test SET NOCOUNT ON between client and SQL, so I tested a similar behavior for other SET command “SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED”

I sent a command from my connection changing the default behavior of SQL (READ COMMITTED), and it was changed for the next commands.
When I changed the ISOLATION level inside a stored procedure, it didn’t change the connection behavior for the next command.

Changing setting by sending commands using the ADOCOnnection changes the default behavior.

I think this is relevant to other SET command such like “SET NOCOUNT ON”

Questions:

Answers:

SET NOCOUNT ON;

This line of code is used in SQL for not returning the number rows affected in the execution of the query. If we don’t require the number of rows affected, we can use this as this would help in saving memory usage and increase the speeed of execution of the query.

Questions:

Answers:

if (set no count== off)

{
then it will keep data of how many records affected
so reduce performance
}
else
{
it will not track the record of changes
hence improve perfomace
}
}

Questions:

Answers:

I know it’s pretty old question. but just for update.

Best way to use “SET NOCOUNT ON” is to put it up as a first statement in your SP and setting it OFF again just before the last SELECT statement.

Questions:

Answers:

SET NOCOUNT ON;
Above code will stop the message generated by sql server engine to fronted result window after the DML/DDL command execution.

Why we do it?
As SQL server engine takes some resource to get the status and generate the message, it is considered as overload to the Sql server engine.So we set the noncount message on.