Avoiding unnecessary overhead with stored procedures
In stored procedures, use the SET NOCOUNT ON notation even when there’s a requirement to return the current row count during execution, as in the following example:
CREATE OR ALTER PROCEDURE [dbo].[uspStocksPerWorkOrder] @WorkOrderID [int] AS BEGIN SET NOCOUNT ON; SELECT wo.StockedQty, wor.WorkOrderID FROM Production.WorkOrder AS wo LEFT JOIN Production.WorkOrderRouting AS wor ON wo.WorkOrderID = wor.WorkOrderID WHERE wo.WorkOrderID = @WorkOrderID; END;
When SET NOCOUNT is ON, the count indicating the number of rows affected by a T-SQL statement is not returned to the application layer, which provides a performance boost.
Note
The @@ROWCOUNT function will still be incremented even with SET NOCOUNT ON.
To put this to a test, we can use the ostress utility and simulate a client application executing...