Tutorial Gateway

SQL SET NOCOUNT ON

by

The SQL SET NOCOUNT ON is one of the Set Function in SQL Server, which is used to stop the number of rows effected message from SQL Query, or Stored Procedure to the client. In general,

  • When you select data from a table, query (or stored procedure) will return those records as the result, and it also display the message as Number of Rows affected
  • If you INSERT 10 records into any table, then SQL will return the message as 10 records effected etc.

By using this SQL 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. This article will show you, How to use the SET NOCOUNT ON in SQL Server with example. Before we get into the example, let us see the syntax behind this:

SQL SET NOCOUNT ON Syntax

The basic syntax of the SET NOCOUNT ON in SQL Server is as shown below:

SET NOCOUNT {ON | OFF }

We are going to use the below shown data for this demonstration

SQL ROWCOUNT 1

SQL SET NOCOUNT ON Example

In this example we will show you, How the SET NOCOUNT ON in SQL Server will effect 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.

T-SQL CODE

USE [SQL Tutorial]
GO 
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employee]

OUTPUT

SQL SET NOCOUNT ON EXAMPLE 1

From the above screenshot you can see that, the table contains 14 records. Now, let us navigate to Message Tab (beside the Result Tab) to check the message.

SQL SET NOCOUNT ON EXAMPLE 2

As you see that, it is displaying the message as: 14 row(s) affected

Let us use the SET NOCOUNT ON Statement

T-SQL CODE

USE [SQL Tutorial]
GO
SET NOCOUNT ON;  
GO  
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employee]

OUTPUT

SQL SET NOCOUNT ON EXAMPLE 3

As you can see, it doesn’t effected the query result. Now, let us navigate to Message Tab to check for the message.

SQL SET NOCOUNT ON EXAMPLE 4

As you see that, there is No message

SQL SET NOCOUNT OFF

Use the SET NOCOUNT OFF; to display the rows effected message

T-SQL CODE

USE [SQL Tutorial]
GO
SET NOCOUNT OFF;  
GO  
SELECT [FirstName]
      ,[LastName]
      ,[Occupation]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
FROM [Employee]

OUTPUT

SQL SET NOCOUNT ON EXAMPLE 5

Thank You for Visiting Our Blog

Placed Under: SQL