SET NOCOUNT For SQL Server

Last week I was reviewing an article and found myself needing information on the use of NOCOUNT as a standard for writing stored procedures. A quick internet search found this old post of mine, written back when I used to work for a living. Apparently, I was once asked to enable NOCOUNT for a specific SQL Server database. As the post suggests, this is not possible. The options for NOCOUNT are to set for the entire instance, for your specific connection, or within your T-SQL code.

Since the post was written well before the new-ish ALTER DATABASE SCOPED CONFIGURATION statement, I was hopeful enabling NOCOUNT for a database was now possible. Turns out you cannot, as the set options listed here do not include NOCOUNT. Sad trombone music.

But of course I tried anyway.

And I failed.

Really failed.

I tried to enable NOCOUNT for my instance of SQL 2019 and it wouldn’t take. At all.

Let me explain.

The Flop

Using the code from my previous post, you enable NOCOUNT for the instance by configuring the user option to 512, like this:

EXEC sys.sp_configure 'user options','512'
GO
   
RECONFIGURE   
GO

Now, open a new query window in SQL Server Management Studio (SSMS), set the results to text to make the output easier to see, and run a query. If you are like me, you will see this:

Not exactly the expected behavior! My initial reaction is to assume I have screwed this up somehow. I decide to try Azure Data Studio (ADS) to connect and run the query:

Same result. Two tools, and the result set is showing a count of rows affected, despite the user option clearly having been set.

And the SSMS GUI verifies this as well:

The Turn

Before I go any further, I want to take note that SET NOCOUNT OFF is one of those horrible phrases we come across in tech where our brains are forced to think twice about what we are doing. Whoever named it this way should be sacked. A simple SET ROWRESULTS ON|OFF would be far simpler to comprehend. </rant>

Anyway, I spend time trying to debug what is happening. I am able to manually set NOCOUNT on and off inside of T-SQL and see a count of rows affected returned (or not). I check and recheck everything I can think of and feel as if I have lost my mind. I’m starting to question how I ever became certified in SQL Server.

I mean, it’s a simple configuration change. This isn’t rocket surgery.

So I do what anyone else in this situation would do.

I turn off my laptop and forget about everything for a few days.

The River

Eventually I decide to reopen my laptop and try again. I am able to reproduce everything. So I ask some friends if they are also seeing similar issues. One friend, Karen López (@datachick), asked me a few follow up questions. These questions get my mind thinking about other ways to test behavior and debug. I suddenly recall I can check for the options set for my current connection:

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Running the above code returns the following result for my connection:

And then it hits me. My connection does not have NOCOUNT enabled! I mean, I’m not really surprised, but it is helpful to see that it is missing. I then decide to open a connection with SQLCMD and observe the default behavior for the connection. Sure enough, NOCOUNT is enabled, as expected:

My connection string has no additional options, and the NOCOUNT is respected. This is the expected behavior for the instance.

Now I need to verify what is happening under the hood when you connect to SQL Server using SSMS or ADS. Using the default xEvents session I capture the connection string sent when connecting from ADS and find this gem:

The NOCOUNT user option configuration item is not recognized when you connect using those tools. Other user options appear to be respected, but for some reason NOCOUNT is ignored. This explains why I was see the unexpected behavior.

I will keep my certifications for now.

Summary

I don’t know if this is a bug or a feature. But it is certainly a frustrating experience for an end user like myself. But if I SET NOCOUNT for SQL Server I expect it to apply to all users connecting from that point forward. Since other user options appear to be respected, there must be something different about NOCOUNT.

It should not matter how users are connecting. SSMS and ADS should both respect the server settings. I suspect other tools likely use the same code as SSMS and ADS, meaning you should double check the actual connection string used from your application. It could explain unexpected behavior.