SET NOCOUNT ON improve SQL Server store procedure performance

Whenever we write the store procedure to make sure SET NOCOUNT is always ON because when SET NOCOUNT OFF then each store procedure statement sending some message back to client and which is not require on all case.

SET NOCOUNT ON

If you require affect row count without SET NOCOUNT ON  then you just need to require add @@ROWCOUNT after statement of execution. So @@ROWCOUNT will return the result of affected record count.

UPDATE student SET Name= 'Kalpesh'
where studentid = 1



PRINT(@@ROWCOUNT)

When you working on Management Studio and require the message on output window then it will be good to SET NOCOUNT OFF because that we require for development purpose only.

SET NOCOUNT OFF

UPDATE student SET Name= 'Kalpesh'
where studentid = 1

 

When we create new store procedure from SQL Server management studio at time store procedure default template also set a SET NOCOUNT ON.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE  
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1>  = , 
 <@Param2, sysname, @p2>  = 
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>
END
GO

asdas