SET NOCOUNT ON | OFF Statement in sql server

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

Set’s see in this demo.

SET NOCOUNT OFF

This shows the number of affected rows in a message window.

    

SET NOCOUNT ON

This prevents the message from showing which contains the number of affected rows.

   

SET NOCOUNT and @@ROWCOUNT function

We can use the @@ROWCOUNT function to get the number of affected rows in SQL Server. The NOCOUNT ON function does not have any impact on the @@ROWCOUNT function.

Execute the following query, and we get the number of rows affected with the Insert statement.