SQL Server SET NOCOUNT ON/OFF is a SET statement that controls the behaviour in SQL Server to shown the number of affected rows in the T-SQL query. By default, SET NOCOUNT is set to OFF.
When SET NOCOUNT is ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When SET NOCOUNT is OFF, the count is returned.
It is used with any SELECT, INSERT, UPDATE, and DELETE statement.
You may have noticed many times whenever you perform any select, insert, update, delete commands or execute any stored procedure, they returns output as results but also displays the message as number of rows affected rows because of SQL Set NOCOUNT OFF. By default, SET NOCOUNT is set to OFF.
For example, if you update 5 records in any table, then SQL will return the message as 5 records affected, that happens because of NOCOUNTÂ is by default set to OFF. So it returns the message which is the count of number of affected rows by the T-SQL query.
By using this SQL Server SET NOCOUNT ON, you can stop the messages. Which really imporves the performance. For any stored procedures that contains bunch of statements that do not return much actual data, or for procedures that contains loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
SYNTAX
SET NOCOUNT {ON | OFF }
Lets look at an example using SET NOCOUNT ON/OFF in SQL, Before that we create a sample table named as DepartmentDetails.
CREATE TABLE [dbo].[DepartmentDetails] ( [Dept_ID] [int] NOT NULL PRIMARY KEY, [Dept_Name] [varchar](100) NOT NULL, [Dept_Code] [varchar](10) NULL )
SET NOCOUNT OFF
Lets see the default behaviour of SET NOCOUNT, when SET NOCOUNT is set to OFF.
We will insert few records into table DepartmentDetails as SET NOCOUNT by default is set to OFF, so it returns the message that indicates the number of rows that are affected by the T-SQL statement (Insert statement).
SET NOCOUNT OFF; INSERT INTO dbo.[DepartmentDetails] (Dept_ID, Dept_Name, Dept_Code) VALUES(501, 'IT', '001'), (502, 'FINANCE', '002'), (503, 'HR', '003'), (504, 'MIS', '004'), (505, 'CALL CENTER', '005'), (506, 'SALES', '006')
As you can see, it returns number of rows that are affected by the T-SQL statement, is 6 rows affected.
Lets see, when you select a records from table, as SET NOCOUNT is still set to OFF so again it will returns a records in grid as result and number of affected rows as message in messages tab.
You can see the message 6 rows affected under messages tab.
SET NOCOUNT ON
When SET NOCOUNT is ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results.
Lets remove an existing records from table first as inserting same records again will give you primary key violation error, or you can insert different records, then execute the same insert script again with setting a SET NOCOUNT ON to see the behaviour of SET NOCOUNT when it set to ON.
TRUNCATE TABLE dbo.[DepartmentDetails]
Once you remove the records from table, then modify insert script by setting SET NOCOUNT to ON as given below.
SET NOCOUNT ON; INSERT INTO dbo.[DepartmentDetails] (Dept_ID, Dept_Name, Dept_Code) VALUES(501, 'IT', '001'), (502, 'FINANCE', '002'), (503, 'HR', '003'), (504, 'MIS', '004'), (505, 'CALL CENTER', '005'), (506, 'SALES', '006')
Once you execute the above T-SQL, this time it does not returns the count of affected rows.
Lets see, for select statement and you can see there are no affected rows count message under messages tab.
SELECT * FROM dbo.[DepartmentDetails]
SET NOCOUNT ON in Stored Procedure
Lets see an example of using SET NOCOUNT ON in Stored Procedure.
First we create a simple stored procedure that returns the number of records in departmentdetails table.
In following stored procedure get_depatmentCount, SET NOCOUNT is set to OFF.
CREATE PROC get_depatmentCount AS BEGIN SET NOCOUNT OFF; SELECT COUNT(1) as [Count] FROM dbo.[DepartmentDetails] END
Lets execute the Stored procedure, and you see it returns the total counts as result and in messages tab it returns the number of affected rows as shown in below screenshots.
EXEC get_depatmentCount
Lets alter the above stored procedure and set NOCOUNT to ON.
ALTER PROC get_depatmentCount AS BEGIN SET NOCOUNT ON; SELECT COUNT(1) as [Count] FROM dbo.[DepartmentDetails] END
After modifying stored procedure for SET NOCOUNT ON. Lets execute it and you can see, this time it does not returns the message for number of affected rows.
EXEC get_depatmentCount
Also Read..
SQL SERVER SET IDENTITY_INSERT
