Comparison triggers T-SQL vs .NET | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Comparison triggers T-SQL vs .NET

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.
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
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
Heute schon gebloggt?
Ich unterstuetze PASS Deutschland e.V.
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 …
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