What are the uses of SET NOCOUNT ON in sql server?

By deafault NOCOUNT will be set to OFF in sql server. This will return number of  rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

This entry was posted in . Bookmark the permalink.

Leave a reply