NOCOUNT In SQL Server

This Statement is used to stop the message that shows the count of the number of rows affected by the SQL statement written in the store procedure or directly SQL statement.You Can view this message in the management studio in the message tab of the result panel.

When it is ON-    The number of affected rows will not be returned
When it is OFF-- The number of affected row will be returned

@@ROWCOUNT- is used to get a number of rows affected. either SET NOCOUNT ON or OFF  
@@ROWCOUNT-- Is alway updated with the number of rows affected


Example

Create Table for performing NOCOUNT Statement

create table tbl_NocuntDemo
  (
  number int
  )

When NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (1),(2),(3),(4)

Output

It return number of rows affected in table in message tab pane

When NOCUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (5),(6),(7),(8)

Output

it does not return row affected in a table but write "Command(s) completed successfully" in message tab pane

so Microsoft think about this error and add a @@ROWCOUNT statement
@@ROWCOUNT is returned number of row affected in table either NOCOUNT ON or OFF

Use @@ROWCOUNT when NOCOUNT is ON

SET NOCOUNT ON
insert into tbl_NocuntDemo values (9),(10),(11),(12)
select @@ROWCOUNT as NoOFRowAffected

Output 
It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4

Use @@ROWCOUNT when NOCOUNT is OFF

SET NOCOUNT OFF
insert into tbl_NocuntDemo values (13),(14),(15),(16)
select @@ROWCOUNT as NoOFRowAffected

Output 

It return @@ROWCOUNT as NoOFRowAffected number of row affected in message
NoOFRowAffected  = 4


select * from tbl_NocuntDemo