1.SET NOCOUNT ON is used in procedure for improve the performance of the store procedure.
put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.
This is performed for all SELECT, INSERT, UPDATE, and DELETE statements.
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 your stored procedures to identify how many rows were affected by the statement.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
@Param1 = <Default_Value_For_Param1, , 0>,
@Param2=<Default_Value_For_Param2, , 0>
-- 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>
Improving SQL Performance