My experience will echo a lot of the comments already mentioned. I use T-SQL whenever possible because, in most cases, T-SQL performs faster, it's easier to maintain and our clients’ IT departments are not as verse with the CLR so they cannot support it. With that said, there has been a few times where T-SQL fell short of our needs and CLR external objects were needed.

I needed a common interface for a collection of applications to easily extract data from a historian which has a proprietary backend database and merge that data with a MES system. The proprietary backend database is a glorified key/value system that is great for tending data over time, but cannot be used for aggregating data or linking multiple keys together to make correlations in the data, which a relational database does naturally. The vendor has built components to overcome these limitations, but I shy away from them because of lower performance, the additional cost and/or they do not really fit into our current architecture. I could have created a .Net DLL for the applications, but by using the SQLCLR, other applications (database applications and .Net applications) could leverage the assemblies. Plus, it was much easier to write a suite of CLR objects to expose the data in the proprietary database and then use T-SQL stored procedures on the exposed data.

Another reason for using the CLR was for reporting. I have written a number of external objects to help format data for SSRS report and to expose analysis services metadata via AMO (primarily role members and role permissions).

There's my 2 cents - Tom

Many of our clients have multiple SQL Servers and I try to install the CLRs on as few servers as possible; usually the servers used for reporting. Having few instances of the assemblies makes it easier to perform updates.