Microsoft added the ability to use the .Net CLR for stored procedures in SQL Server 2005. Is anyone using the CLR for this? If so, why? If not, why not?
That said, we're using it. There are quite a few gotchas that you need to be aware of before deciding to a) enable the CLR and b) choose what you code. From the dba perspective, here is a technet article speaking about CLR Integration security that discusses a lot. Specific topics in the article address:
- CLR Integration Code Access Security
- Host Protection Attributes and CLR Integration Programming
- Links in CLR Integration Security
- Impersonation and CLR Integration Security
- Allowing Partially Trusted Callers
- Application Domains and CLR Integration Security
Don't know if you're still interested in this question, but... Yeah, we've built an entire library of finance, stats, math, engineering and string functions specifically for SQL Server using CLR - a total of 450 individual functions and counting. Our primary motivator is to move the logic from the app tier into the DBMS: reduce network overhead, improve processing speed and work with better data. And of course, to reduce those (in)famous Excel cut/paste catastrophes. There are some cases where you might do the job in T-SQL but the debugging would probably drive you insane. Then there are some other cases where the functions simply are not possible without CLR.