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, 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. My guidelines on this will start of as: - use T-SQL for all set processing - try to use set processing whereever possible - 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 and then I’d refine them as I found more reasons to use .NET… 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=’;-)’ /> Cheers 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
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

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, 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=’;-)’ /> 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… Cheers Twan

]]>