SQL Server Performance

How to hide triggers from SQL Server temporarily?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by pcsql, Jun 22, 2006.

  1. pcsql New Member

    Hi,

    I wonder whether there is a way to temporarily hide triggers from SQL Server 2000, SQL Server 2005 and SQL Server 2005 Express. The reason that I want to do this is because even though I disable triggers, SQL Server will take times to evaluate whether the trigger is disabled or not. Based on my own testing, an alter table statement can be 2 to 3 times faster without trigger comparing to disabled triggers.


    Thanks for any help,

    Peter
  2. FrankKalis Moderator

  3. dineshasanka Moderator

    You want to hide and still that functionality should be there isn't it?
    I don't think you can do that.

    ----------------------------------------
    http://dineshasanka.blogspot.com/
  4. Madhivanan Moderator

    If you think you dont need triggers them as said script them and drop them

    Madhivanan

    Failing to plan is Planning to fail
  5. satya Moderator

    Frank's reference is right:

    alter table <table_name> disable trigger {<trigger_name> | all}


    alter table <table_name> enable trigger {<trigger_name> | all}


    Well that was in Pre SQL 2005 and in 2k5 referhttp://msdn2.microsoft.com/en-us/ms189748.aspx link.
    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  6. pcsql New Member

    I just want to temporarily remove the triggers and then add them back later but these must be done programmatically in T-SQL. Unfortunately, I have not figured out how to do it in T-SQL. Note: Some of the triggers are over 8000 characters long.

    I think I may able to use Dynamic SQL based on the post from my another thread that I posted. Need to try it out.


    Thanks,

    Peter

Share This Page