What does SET NOCOUNT ON do?

T-SQL
13 Comments

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.

What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation.

I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote:

When this stored procedure runs, it does two things: insert rows into Votes, and update rows in Users. When it finishes, it returns two messages in SSMS:

However, if I add SET NOCOUNT ON at the top of the stored procedure:

Then those messages go away, and SQL Server just reports that the query completed successfully:

This means less data gets sent from SQL Server, across the network, over to the application.

How much less data? Well, honestly, not a lot. In most cases, this isn’t a heroic performance change because it doesn’t make a meaningful performance difference. However, if you have code that works in a loop, or modifies a lot of different tables, then it starts to matter more.

You might say to yourself, “Well, then, I only need to add it when I’m working in a loop, or when I’m modifying a lot of different tables.” However, you can’t always predict who’s going to call your code, and they might run YOUR code in a loop – and in that case, reducing the message overhead helps.

That’s why it’s a good practice to start every stored procedure with SET NOCOUNT ON. Get in the practice, and it’s one less thing you’ll have to worry about as your workload scales.

Previous Post
Download the Current Stack Overflow Database for Free (2021-06)
Next Post
Free Video: 300-Level Guide to Career Internals: Planning Your Career

13 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu