SQL SET NOCOUNT ON

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 XACT_ABORT

SQL SERVER SET DATEFORMAT

SQL SERVER SET IDENTITY_INSERT

SQL Server Stored Procedure

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 6,588 total views,  1 views today