The SQL SET NOCOUNT ON and OFF is one of the Set Function, which is used to stop the number of rows affected message from a query or Stored Procedure to the client.
In general, When you select data from a table, a stored procedure or query will return those records as the result. And it displays the message as Number of Rows affected because of SQL Set Nocount On. For instance, If you INSERT 10 records into any table, then SQL will return the message as ten records affected, etc.
By using this SQL Server SET NOCOUNT ON, you can stop the above messages. In real-time, it is an extra load to display those messages. So, try to use SET NOCOUNT ON for better performance. Before we get into the SET NOCOUNT ON and SET NOCOUNT OFF example, let us see the syntax behind this:
SQL SET NOCOUNT ON Syntax
The basic syntax of the SET NOCOUNT ON is as shown below:
SET NOCOUNT {ON | OFF }
We are going to use the below-shown data for this demonstration
SQL SET NOCOUNT ON Example
In this example, we show you how the SET NOCOUNT ON will affect the queries. Before we get into the main example, let me write a simple SELECT Statement to show you the message that is displayed by the SQL.
SELECT [FirstName]
,[LastName]
,[Occupation]
,[Education]
,[YearlyIncome]
,[Sales]
FROM [Employee]
From the above SQL Server screenshot, you can see that the table contains 14 records. Now, let us navigate to Message Tab (besides the Result Tab) to check the message.
It is displaying the message as 14 row(s) affected. Let us use the SET NOCOUNT ON Statement
SET NOCOUNT ON;
GO
SELECT [FirstName]
,[LastName]
,[Occupation]
,[Education]
,[YearlyIncome]
,[Sales]
FROM [Employee]
It doesn’t affect the query result. Now, let us go to Message Tab to check for the message.
As you see that, there is No message such as 14 rows affected
SQL SET NOCOUNT OFF
Use the SET NOCOUNT OFF to display the rows affected the message
SET NOCOUNT OFF;
GO
SELECT [FirstName]
,[LastName]
,[Occupation]
,[Education]
,[YearlyIncome]
,[Sales]
FROM [Employee]
Please refer to Stored Procedures and Insert Statement articles.