NOCOUNT Command in sql server

NOCOUNT command returns number of rows affected by T-SQL statements after the command execution.

Syntax for NOCOUNT is :

SET NOCOUNT {ON/OFF}

SET NOCOUNT OFF :  By default NOCOUNT option will be set to OFF in sql server. When ever a T-SQL statement is executed in sql server, then it will return the count of number of rows affected after the statement completion.

SET NOCOUNT ON :  This command will not return the count of the number of records affected after command execution.

This option will reduce the extra overhead to the network , which will improve the performance of the sql code.

SET NOCOUNT ON option is especially useful in case of stored procedures.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.  By issuing a SET NOCOUNT ON this function @@ROWCOUNT still works and can still be used in stored procedures to fetch information about how many rows were affected by the statement.


This entry was posted in . Bookmark the permalink.

Leave a reply