Lowell (1/20/2010)
CLR is much faster with string manipulations. there are some things that cannot be done in TSQLRegular expressions is one of the most common example, where it cannot be done natively, and is also much faster for doing string manipulations that could be done in TSQL anyway.
there is a great performance measure made by some of the top posters that compared TSQL to CLR performance wise, including Tally table measures, that showed a good example of a situation where CLR was faster; i
batches of nested find and replace are also faster. REPLACE(REPLACE(REPLACE(....kind of stuff.
also, there is no data from Microsoft which says that there may be performance improvements if the logic was implement as a SQLCLR SP rather than a normal C# business object.
also, just because Microsoft doesn't paint themselves in a corner by saying something is faster or not, does not make it false...they just don't say it because it may not cover every situation. "CLR is faster" is not true all the time.
Not all string manipulations are faster with CLR's. Not all Regex is faster than T-SQL. How do I know those things? I'm one of the folks you're talking about and Matt Miller and I had great fun testing these things. Unfortunately, both he and I have lost track of most of those tests and haven't been able to find them on this fine forum again.
What I HAVE found is that some people use CLR's to make up for their lack of knowledge of how to do something in T-SQL. I've also found that some of the "Ninjas" on this forum do use them correctly for things like file handling and the like.
--Jeff Moden
Change is inevitable... Change for the better is not.