T-SQL Hygiene: What’s the Big Deal with SET NOCOUNT ON?

Perhaps you have seen SET NOCOUNT ON; at the beginning of a stored procedure and wondered why it is there. By default, SQL Server uses SET NOCOUNT OFF;, which sends messages such as “(10 rows affected)” after each statement. Using SET NOCOUNT ON; suppresses those messages.

For example

If an update changes 10 rows, with row-count messages enabled:

SET NOCOUNT OFF;

SQL Server sends:

(10 rows affected)

And with row-count messages suppressed:

SET NOCOUNT ON;

SQL Server sends no row-count message.

A developer’s perspective

In a .NET or Entity Framework application, the affected-row messages are usually ignored. Your code typically cares about returned data, output parameters, return values, or an explicitly returned row count. Suppressing unused messages can reduce unnecessary work for SQL Server, the network, and your application.

A simple walk-through

Consider this stored procedure, which closes old orders and returns the number updated:

CREATE PROCEDURE dbo.CloseOldOrders AS
BEGIN
    SET NOCOUNT ON;

    UPDATE dbo.Orders
    SET Status = 'Closed'
    WHERE ShipDate < GETDATE();

    SELECT @@ROWCOUNT AS RowsUpdated;
END;

SET NOCOUNT ON; does not prevent the update, suppress the SELECT, disable @@ROWCOUNT, or change the result of the procedure. It only suppresses the extra affected-row message.

Better performance at scale

The savings from one statement are small, but they add up. A procedure with 20 statements called 100,000 times could avoid about 2 million unnecessary row-count protocol tokens. These tokens may share network packets, but they still add processing overhead. Loops, triggers, multiple updates, and nested procedures can generate even more. Removing them can reduce network traffic, client processing, latency, and compute usage.

Safe and easy

Inside a transaction, SET NOCOUNT ON; does not affect locking, commits, rollbacks, or error handling. It only controls whether affected-row messages are sent.

CREATE PROCEDURE dbo.CloseOrder
    @OrderId int
AS
BEGIN
SET NOCOUNT ON;

    BEGIN TRANSACTION;

        UPDATE dbo.Orders
        SET Status = 'Closed'
        WHERE OrderId = @OrderId;

        INSERT INTO dbo.OrderHistory (OrderId, Status)
        VALUES (@OrderId, 'Closed');

    COMMIT;

    SELECT @OrderId AS OrderId, 'Closed' AS Status;

END;

With SET NOCOUNT OFF;, the procedure sends two affected-row messages before returning the final order status. With SET NOCOUNT ON;, it returns only the final order status.

The transaction behaves exactly the same either way. NOCOUNT simply prevents the intermediate messages that your application probably ignores.

Conclusion

SET NOCOUNT ON; is a small change with a real benefit. It removes messages your .NET application usually does not need, reduces unnecessary work, and keeps stored procedure behavior more predictable. For most stored procedures, it should be the default unless you specifically need those messages.