A developer once "proved" to me how much faster CLR was. He developed a simple TSQL function and a CLR function that did equivalent things. His test script showed the CLR function to be roughly 20 times faster than the TSQL function.

On closer examination of what was wrapped around the functions in the test scripts, there were further efficiencies to be gained. For example, the CLR function was only being called "when needed", but the TSQL function was called for every record.

Once the maximum efficiencies were included, the TSQL sample ran slightly faster than the CLR sample.

We do some specialty stuff which requires developing 20,000-30,000 lines of TSQL code in our test environment, moving that to a customer's test environment, then ultimately moving that to the customer's live environment and running it one time (data conversion). Therefore the code needs to be very portable.

Collecting the full set of stored procedures into one text file (we have a script that does this) has proven to be a very effective method for portability. (Server names can also be changes by a simple search/replace across the text file.) CLR adds another level that would also have to be ported.

In short, our developers can use CLR if desired and if effective for what they need to do. Because they also deliver the solution, they also have to work with the portability aspects. To date, only two of over 100 projects has used CLR.