This is a setting that DBA are advised to place at the start of the stored procedure.

What this setting does?

Let us run an sample code for this.

USE AdventureWorks
GO
SELECT * FROM Person.Address

SET NOCOUNT ON

SELECT * FROM Person.Address

SET NOCOUNT OFF

You will that first select query (at line no 3), will execute with out the the above setting while second SELECT statement (at line no 7) will execute with the SET NOCOUNT ON setting.

Though both queries will result same output. So what is the different.

If you got the message tab, you will see following output there.

Only first query has returned the number of rows effected not the second query.

This is what BOL says,

“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.”

In simple term, SET NOCOUNT ON will not return number of records affected to the client. What is the bug deal. If you are not using this information, it is an unnecessary burden to your server and network. By not returning this information, you are using less resources. This might not be huge saving, but might be the string which will brake the back of the camel.

How to Implement

It is advisable to implement  this in each stored proc as follows.

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
	SET NOCOUNT OFF;
END
GO

Make sure you SET NOCOUNT to OFF at the end of the stored proc.

Following query will list you all the stored procedures which does not have SET NOCOUNT ON statement.

SELECT DISTINCT
        OBJECT_NAME(id)
FROM    syscomments c
        INNER JOIN sys.objects o ON c.id = o.object_id
WHERE   text NOT LIKE '%SET NOCOUNT ON%'
        AND type = 'P'
        AND id NOT IN ( SELECT  id
                        FROM    syscomments c
                                INNER JOIN sys.objects o ON c.id = o.object_id
                        WHERE   text LIKE '%SET NOCOUNT ON%'
                                AND type = 'P' )