SQL Server – Why SET NOCOUNT ON Should Be in Every Stored Procedure

SET NOCOUNT ON is a SQL Server command that stops the message showing how many rows were affected by a query. It improves performance by reducing unnecessary communication between server and client.

• Default value is OFF
• Suggested Value is ON (Put at the top of each SQL Objects e.g Stored Procedure)
• Commonly used in stored procedures, functions, and triggers to make execution cleaner and faster.
• By default, NOCOUNT is OFF, meaning the row count message is shown unless explicitly turned off.
• It helps reduce network latency, especially in loops.
• It has no impact on the actual result set or data changes — only suppresses metadata output
• It is considered a best practice to include SET NOCOUNT ON at the beginning of stored procedures.

Output Comparison: SET NOCOUNT ON vs OFF:

1) When SET NOCOUNT OFF:

USE BI_Reporting;
GO

SET NOCOUNT OFF;
GO

-- Display the count message.
SELECT [EmpName]
FROM [dbo].[Employee]
WHERE [Department] = 'IT';
GO

Result- (4 rows affected) message is coming

2) When SET NOCOUNT ON:

-- SET NOCOUNT to ON .
SET NOCOUNT ON;
GO
SELECT [EmpName]
FROM [dbo].[Employee]
WHERE [Department] = 'IT';
GO