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
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757