SQL Server Interview Question #107

Why is SET NOCOUNT ON commonly used in stored procedures?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

SET NOCOUNT ON prevents SQL Server from sending row-count messages such as '(10 rows affected)' after each statement in the procedure.

This can reduce unnecessary network messages and avoids some client-side complications when procedures contain multiple statements. It does not prevent the application from retrieving an explicit count using COUNT, @@ROWCOUNT, output parameters, or a result set.

Code Example

CREATE PROCEDURE dbo.GetActiveProducts
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Price
    FROM dbo.Products
    WHERE IsActive = 1;
END;