Jeff Moden (12/23/2009)
I'm not picking on the author here... it's a general statement that I bring up to anyone who may be considering the use of CLRS... Don't use the excuse of a lack of knowledge of T-SQL functionality as a justification for CLR usage. You'll frequently pay (yes, there are exceptions) for such a mistake in the form of performance and resource usage. There are very few things that can't actually be done in T-SQL and there are very few things where a CLR will actually win the performance foot race if the T-SQL is properly written for performance. Even when T-SQL does lose the performance footrace, it's many times so close to the performance of CLR's that it's just not worth the time and effort to maintain a separate code base outside of T-SQL.
A good point and well taken. Still there are some times where CLR can make sense, particularly if you want to do things with Regex for instance.
Also, Itzik Ben-Gan showed that at least for some types of large data sets CLR outperforms T-SQL in running aggregates. There are some details here: http://www.sqlmag.com/articles/index.cfm?articleid=102336 (subscription required.)
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/