What does “SET NOCOUNT ON” do in SQL Server? Explained!

Maybe you’ve been working with SQL Server for a while and have seen “SET NOCOUNT ON” here and there, but you aren’t really sure what it does.

It’s usually seen within stored procedures, but it can be used anywhere.

In this very brief tutorial, we’ll discuss what the SET NOCOUNT ON setting does and how it can be very helpful.

We’ll discuss these topics:

1. What does “SET NOCOUNT ON” do in SQL Server?
2. An example of using SET NOCOUNT ON
3. Tips, tricks, and links

Starting from the top:

1. What does “SET NOCOUNT ON” do in SQL Server?

When the NOCOUNT setting is turned on (by running SET NOCOUNT ON), it stops the informational messages from appearing that tell you how many rows were effected by a T-SQL statement. 

Whenever we run an INSERT or an UPDATE statement, for example, SQL Server likes to tell us how many rows were effected by the statement. Normally, this is a good thing to see. For example, if we meant to update 1 row, but SQL Server tells us we actually updated 100 rows, that would be good information to know (oops).

2. An example of using SET NOCOUNT ON

We’ll create a very simple table called Books to demonstrate:

CREATE TABLE Books
(
BookID INT IDENTITY(10,5),
Title VARCHAR(35),
Author VARCHAR(15),
Pages INT
)

We’ll insert a few row into this table. By default, the NOCOUNT setting is off, which means we will see information messages after each INSERT statement. Here are the rows to insert:

INSERT Books (Title, Author, Pages) VALUES ('As a man thinketh', 'Allen', 45)
INSERT Books (Title, Author, Pages) VALUES ('Eat that frog', 'Tracy', 108)
INSERT Books (Title, Author, Pages) VALUES ('The war of art', 'Pressfield', 165)
INSERT Books (Title, Author, Pages) VALUES ('Deep work', 'Newport', 263)
INSERT Books (Title, Author, Pages) VALUES ('The Pragmatic Programmer', 'Thomas', 283)
INSERT Books (Title, Author, Pages) VALUES ('The Education of a Bodybuilder', 'Schwarzenegger', 256)
INSERT Books (Title, Author, Pages) VALUES ('Debt Free Degree', 'O''Neil', 224)

Here is a screenshot of me running those statement. You can see all the messages that display after each INSERT statement:

If we enable the NOCOUNT setting by running SET NOCOUNT ON, then insert a few more rows, we see that these rows will not display those information messages. Here are the rows we’ll insert:

INSERT Books (Title, Author, Pages) VALUES ('Brain on Fire', 'Cahalan', 266)
INSERT Books (Title, Author, Pages) VALUES ('Killers of the Flower Moon', 'Grann', 377)
INSERT Books (Title, Author, Pages) VALUES ('Mustang Man', 'L''Amour', 189)

And here’s the example of setting NOCOUNT to ON then inserting those rows:

The only message we see is “Commands completed successfully“. All those other messages saying “(1 row affected)” are suppressed!

Turning NOCOUNT back off is simple. Just run SET NOCOUNT OFF:

It’s common to enable NOCOUNT within a stored procedure

I normally see the SET NOCOUNT ON statement within stored procedures. Your stored procedures might end up inserting/updating many rows, and maybe we don’t want to burden the caller of that procedure with all those messages that they probably don’t care about anyway.

It’s common to run SET NOCOUNT ON right at the top of a procedure to suppress those information messages whenever the procedure runs any DML statements.

We’ll create a very simple procedure to insert a single row into the Books table. The column content will simply be passed into the procedure as parameters, and we’ll use those parameters in an INSERT statement within the body of the procedure. Here it is:

CREATE OR ALTER PROCEDURE InsertBook 
@titleVal VARCHAR(35), @authorVal VARCHAR(15), @pageCount INT
AS
INSERT INTO Books (Title, Author, Pages) 
VALUES
(@titleVal, @authorVal, @pageCount)
GO

Notice we haven’t enabled NOCOUNT yet. If we execute this stored procedure, we see the information message letting us know a row was inserted:

Now we’ll alter the procedure to enable NOCOUNT:

Then we’ll execute the procedure again to insert another row and see that the information message is suppressed:

We can look in the table and see that the row was indeed inserted into the table:

Superb!

3. Tips, tricks, and links

Here are a few tips you should know about the NOCOUNT setting:

Tip # 1: The NOCOUNT setting is scope specific

In that last example, notice we didn’t turn NOCOUNT back off at the end of the procedure.

When we call a stored procedure, that procedure runs within it’s own scope. So if we enable NOCOUNT within the procedure, that’s the only place the NOCOUNT setting will affect.

Also remember that the NOCOUNT setting is off by default. This means when the flow of control comes back to the caller of the procedure (which is in it’s own separate scope), the NOCOUNT setting will likely be off there.

If you wanted to be explicit, you could turn off NOCOUNT at the end of your procedure by running SET NOCOUNT OFF. But understand you likely don’t need to.

Tip # 2: Enabling NOCOUNT can improve performance

I’ve read that enabling NOCOUNT can actually improve performance, especially when it comes to executing stored procedures. If this setting is enabled, SQL Server doesn’t need to send information “over the wire” after every DML statement, which results in quicker execution times.

Links:

Here is the official link to the Microsoft Documentation on the NOCOUNT setting:

SET NOCOUNT (Transact-SQL)

Next Steps:

Leave a comment if you found this tutorial helpful!

As mentioned, the NOCOUNT setting is usually seen within stored procedures. Make sure you take a look at the full beginner-friendly tutorial on SQL Server stored procedures:

SQL Server Stored Procedures: The Ultimate Guide for Beginners

And I really hope you read this tutorial, too:

Don’t make this HUGE mistake within your stored procedures!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!