What is SET NOCOUNT in SQL Server

You must have seen the statement SET NOCOUNT ON in most of the stored procedure. When I saw this message first time I was thinking why this has been used in almost all stored procedure what will happen if I set SET NOCOUNT OFF in place of ON. Here in this article I thought I will share you the advantage of using SET NOCOUNT ON in SQL Stored Procedure and how this will improve the performance of your query.

The main advantage of using SET NOCOUNT ON in your stored procedure or TSQL statement is, this statement automatically suppress the message which shows how many rows has been affected by the query. Which means, this will reduce the overhead of the server and improves the performance when you execute query in a table which has got millions of data.

What exactly is happening inside the server is when you execute a stored procedure with SET NOCOUNT ON  it prevents the sending of DONE_IN_PROC messages to the client for each statement in your stored procedure. Imagine you have a loop or several statement has to be executed!!! So using SET NOCOUNT ON will definitely boost your query performance and adding the line SET NOCOUNT ON is one of the main DBA tasks when they find that query or procedure is not performing well.

If you wanted to know the message of affected rows then you can add SET NOCOUNT OFF in your query. This will show the number of rows affected by executing the query.

Irrespective of using SET NOCOUNT ON or SET NOCOUNT ON  SQL server internally updates @@ROWCOUNT function. So querying @@ROWCOUNT function will give you the affected rows details at any time.

Now let’s see how it shows in SQL query analyzer,

Below is my stored procedure, you can notice that I have commented the line SET NOCOUNT ON. 

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    --SET NOCOUNT ON;

    SELECT * from Customer

END

We will execute this procedure and see the result.

Now we will un comment the line SET NOCOUNT ON and see the output,

CREATE PROCEDURE [dbo].[sp_GetCustomerList]

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * from Customer

END

Hope you are now clear about the usage of SET NOCOUNT ON.