Benefits of Set No Count on

By default the no count setting will be OFF in sql server for any procedure execution. What is happening when we execute any procedure , Sql server connect and send back information to client and client to server. This allows client to show the record set along with message in different tab but this message transferring activity creates an extra over-head on network. We can reduce this overhead by turning  ON to no count so the procedure will get much better performance in execution.

Eg,,

       

Create procedure 

DailyObjectsChangeLog

as

Begin



SET NOCOUNT ON;



select name,object_type,last_dt_action,CONVERT(date,getdate()) as checked_dt into #abc from (

select name,case 

when type ='U' then 'UserTable'

when type ='V' then 'View'

when type ='P' then 'Procedure'

end as object_type,'Created' as last_dt_action

from sys.objects where type in ('U','v','P')

and CONVERT(date,create_date)=CONVERT(date,getdate()-1)

union

select name,case 

when type ='U' then 'UserTable'

when type ='V' then 'View'

when type ='P' then 'Procedure'

end as object_type,'Changed' as last_dt_action

from sys.objects where type in ('U','v','P')

and CONVERT(date,modify_date)=CONVERT(date,getdate()-1)

)a 



insert into [object_change_log] 

select * from #abc



End