SQL Server: SET NOCOUNT ON Statement with Examples
SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements. This is used within stored procedures and triggers to avoid showing the affected rows message. Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a significant margin.
SET NOCOUNT ON: This prevents the message from showing which contains the number of affected rows.
SET NOCOUNT OFF: This shows the number of affected rows in a message window.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.
For stored procedures that contain several statements that do not return much actual data or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
SET NOCOUNT ON/OFF with an Example
Using SET NOCOUNT ON in the first T-SQL statements prevents showing the number of affected rows message.
SET NOCOUNT with @@ROWCOUNT Function
This does not have any impact on the @@Rowcount function which shows the number of affected rows within a Batch.
Using the @@ROWCOUNT function within SET NOCOUNT ON does not reset the @@ROWCOUNT value. The NoOfRowsAffected column is showing four rows were affected by the previous INSERT statement.
Using SET NOCOUNT ON/OFF within a Stored Procedure
Executing the Stored Procedure Above
You can see in the screenshot above that the executed stored procedure does not show the number of affected rows message. Using SET NOCOUNT ON within stored procedures is recommended as best practice for performance tuning in SQL Server.
You can browse our SQL Server archive articles for more useful information.
Follow the reactions below and share your own thoughts.