SQL Server Performance

Comparison triggers T-SQL vs .NET

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by gus, Mar 16, 2006.

  1. gus New Member

    Hi there.

    I was planning to migrate some triggers from T-SQL into .NET embedded code.

    Is there any comparison between T-SQL vs .NET embedded code ?

    I know this question is pretty vague and it depends whether it's computational intensive or data intensive, but maybe somebody can point me to a serious comparison to start with.

    Thanks.

  2. Twan New Member

    Hi ya,<br /><br />I'm not aware of any hard and fast rules, but to be honest this is exactly the sort of thing I dreaded when I heard about SQL allowing .net code within procs, etc.<br /><br />My guidelines on this will start of as:<br />- use T-SQL for all set processing<br />- try to use set processing whereever possible<br />- use .NET for cursor processing if each row needs non-sql transformations, e.g. encryption or regular expression manipulation of a field can be easily done in .NET but is more difficult or less efficient in T-SQL<br /><br />and then I'd refine them as I found more reasons to use .NET... <br /><br />for the .NET dudes out there, please note that I come from a development background and am not a pure DBA <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  3. FrankKalis Moderator

    This is exactly what all books about SQL Server 2005 I've reviewed so far also preach. Don't use the CLR just because you now can. Evaluate on a case by case basis, when to move from T-SQL to CLR.

    Having that said, I also don't know of any reliable comparisons right now.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
  4. Adriaan New Member

    Twan,

    I'm no .NET dude either, but I'll second your guidelines! I'm still waiting to see some real-world experiences with .NET in triggers ...
  5. Twan New Member

    Hi Guys,<br /><br />Don't get me wrong I think it is really cool that we CAN use .NET within procs and triggers, but it is going to be a performance/maintenance/support nightmare if not managed carefully... I guess this is good for consultants like myself, as it is bound to keep us busy over the coming years <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />I can see some useful applications for this like the use of COM objects, and using some of the built-in .NET features like encryption which will be fantastic for certain specific cases...<br /><br />Cheers<br />Twan

Share This Page