SQL Prompt implements two static code analysis rules to check code for potential misuse of the
SET NOCOUNT command:
Whenever you execute a query, a short message is returned to the client with the number of rows that are affected by that T-SQL statement. When you use
ON, this message is not sent. This can improve performance by reducing network traffic slightly. It is best to use
ON in SQL Server triggers and stored procedures, unless one or more of the applications using the stored procedures require it to be
OFF, because they are reading the value in the message.
ON doesn’t affect the result that is returned. It only suppresses the extra packet of message information, which is otherwise sent back to the client as a small (nine-byte) message packet called
DONE_IN_PROC, for each statement executed. The server-based logic, and values such as
@@ROWCOUNT, are all unaffected.
NOCOUNT is set to
OFF, at the SQL Server instance level, meaning that
DONE_IN_PROC messages will be sent to the client for each statement in a stored procedure. When using the utilities provided with Microsoft SQL Server to execute queries, the message ‘nn rows affected‘ will, by default, be displayed at the end of Transact-SQL statements such as
Microsoft recommends selective use of SET NOCOUNT ON, at the session level, to prevent sending these messages: “For stored procedures that contain several statements that do not return much actual data, eliminating these messages can provide a significant performance boost, because network traffic is greatly reduced”.
The best approach, generally, is to prevent rowcount messages being sent, unless they are required, but the tricky part is accommodating legacy applications that use, and often misuse, these messages. Additionally, sending these messages can sometimes be a problem for asynchronous processing of procedures by intermediate layers of database applications such as ORMs. The rowcount messages are much slower to be transmitted to the client than the result of the stored procedure, and this can block threads.
What is a message?
A connection to a database passes data and messages separately. In Management Studio (SSMS) these are represented by separate panes in the query window, when queries results are rendered as a grid. When you make a sqlClient connection, the message stream can be read by the InfoMessage handler. For the client to be able to read and process warnings or messages sent by the server, it can listen for these messages via a SqlInfoMessageEventHandler delegate that can respond to these events.
In this article, we are only concerned with one type of message; the rowcount. However, SQL Server can also send messages in response to specific commands; including Raiserror (severity of 10 or less), as well as
STATISTICS trio of statements (
ON). My article Routine SQL DML Testing for the Unenthusiastic Tester illustrates how valuable it can be to use this stream of messages when monitoring performance. The
NOCOUNT command determines only whether the rowcount messages are sent.
Several applications, components, widgets (such as grids), and middleware (such as ORMs) use the rowcount message to get a count of the current data result, even though it is often difficult to match the query with the count message when the same session is doing a lot of other queries, and it can also cause blocked threads. It is far better to switch off these rowcount messages using
ON and return the counts using the value of
@@ROWCOUNT, either in the return code of the procedure or by an output variable. However, there is a lot of legacy code that needs to be accommodated.
What is the scope of a SET NOCOUNT setting?
Once you make a connection to SQL Server, and start a session, you need only set
NOCOUNT once, and it will affect everything you do in that session. The
SET statements that you make will change just the current session’s handling of specific information; every batch you execute in that session will inherit these settings.
SET statement is run in a stored procedure or trigger, the previous value of the
SET option is restored after control is returned from the stored procedure or trigger. Likewise, if you have a
NOCOUNT statement in a dynamic SQL string that is run by using either
EXECUTE, then the initial value of the
SET option is restored after the dynamic SQL string has been executed. For this reason, there is no need to explicitly set the
NOCOUNT at the end of a stored procedure or trigger.
Other than procedures, triggers and dynamically executed batches, every setting of
NOCOUNT remains in place within the session until it is changed.
What is the performance advantage of SET NOCOUNT ON?
With well-designed stored procedures, you will see only marginal performance gains from overriding the default server-wide setting for
NOCOUNT. That said, in exceptional circumstances, the gains from using
ON will be significant. It all depends on the number and frequency of queries that are executed within the procedure. For example, if a procedure is using a cursor to perform a lot of queries whose results then go to make up part of the returned query, or if the procedure contains many statements that do not return much actual data, then it can perform at up to ten times the speed, compared to having
OFF, because network traffic is greatly reduced. With just one or two queries in the procedure, the gain will be less than five percent.
Why not just enable NOCOUNT at the database instance level?
The user options sever configuration setting specifies ‘global defaults’ for each of the
SET options, including
NOCOUNT. By default, a SQL Server instance will have
NOCOUNT disabled, and so each statement issued against a database on that instance will result in a message returned at the end, stating how many rows were affected.
You can modify the instance-level behavior, enabling
NOCOUNT and so preventing these messages from being sent, using
sp_configure, as shown in Listing 1. This will affect the default settings for all user sessions that start after the setting was made.
EXEC sys.sp_configure 'user options', '512'; -- 512 = NOCOUNT
Users can override the server-level default, by issuing a
NOCOUNT statement that affects only their individual sessions.
Triggers should not send rowcount messages; there are no exceptions to this rule. In fact, if the intermediate application layer is expecting certain row count messages, and you use
OFF for triggers, this can cause strange random bugs. Even SSMS’s data grid can fall foul of the trigger problem.
However, elsewhere, there are plenty of exceptions. Preventing these messages at the instance level could cause problems if you have any legacy components accessing your databases that make use of the returned rowcount message. Often, these can easily be accommodated by setting the
NOCOUNT as appropriate for the stored procedures being used by these components. However, if they are accessing tables directly and you have no means of adding a
OFF for those sessions, then changing the database instance-level setting would be unwise.
Also, if a component within the application, such as an ORM or LINQ, is misusing this message to determine the row count of the result, then several bad things can happen if you turn the messages off.
What can break if you turn NOCOUNT on?
If you are using a DataAdaptor to call a SQL Server stored procedure, to edit or delete data, do not use
ON in the stored procedure definition. This causes the rows affected count returned to be zero, and the DataAdapter throws a DBConcurrencyException. In fact, sensible defensive programming will mean issuing an explicit
OFF in these cases.
The sqlclient.sqlcommand class can also suffer from problems with
ON, probably caused by the way that the client is using ODBC. The rowcount message is made available in ODBC when an application calls SQLRowCount. It isn’t reliable information because some data sources cannot return the number of rows in a result set before fetching them.
Even in SQL Server, this value is only reliable if you subsequently test the ‘
NOCOUNT status’ after reading the SQLRowCount. When the
NOCOUNT option is set to on, then SQLRowCount returns 0, even though there were results. If SQLRowCount returns 0, the application should find out if
ON by testing the value of the SQL Server-specific
SQL_SOPT_SS_NOCOUNT_STATUS attribute. If the value
SQL_NC_ON is returned then the value of 0 from SQLRowCount merely means that SQL Server has not returned a rowcount. If
SQL_NC_OFF is returned, it means that
NOCOUNT is off and the value of 0 from SQLRowCount indicates that the statement did not affect any rows, and so there is no need to process the result.
So, what’s the ‘best practice’ advice?
The simple advice that works well is to leave the database instance-level default as-is, and to add a
ON at the start of every stored procedure, trigger and dynamically executed batch. This rule will apply to all triggers, without exception. No stored procedures will need these messages either, unless they are called from outside the database by an application that is trying to use them to get a gauge of the number of rows of a result. In general, it is better to send the count in an output variable, using the value in @@RowCount, but this doesn’t help pre-existing application components. If you need to ensure that a query returns a rowcount message, you should specify it rather than assume the current setting.