How to Speed-up Triggers in SQL

Triggers are used to implement business rules, automate or to enforce referential integrity in database applications. Some important points/info about Triggers are -

1) Avoid using cursor in Triggers.

2) Avoid executing many sql statements on other tables also.

3) Avoid returning multi-row-resultset or resultset.

4) SET NOCOUNT ON to avoid return of resultset.

And not to forget - 
5 )  Specify the order of execution of Triggers (After) in case many triggers present on single table/query action.

6) Triggers runs faster like stored procedures becuase it is compiled into execution plan and keep into memory.
   i. This memory management is done by SQL server in FIFO basis.

 

blog comments powered by Disqus