Home / Software / Tips and Tricks / What is the advantage of using "SET NOCOUNT" in a SQL Server Stored Procedure?

What is the advantage of using "SET NOCOUNT" in a SQL Server Stored Procedure?

Updated:  07/29/2016 07:07 AM
Author:  Shiju Mathews

Status:    Resolved.


As more and more users access the databases, the databases continue to grow, performance slow down. Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.

This code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application. Please see the template bellow.



References : https://msdn.microsoft.com/en-us/library/ms189837.aspx