Moving C# in Process Functions to SQL Server CLR functions

Question

What are the limitations, unexpected pitfalls and performance traits of moving from in process C# code to SQL CLR Functions?

We currently have several data heavy processes that run very fast using in process C# Asp.net MVC project without using a db at all. Performance is very important. The application uses a static in memory cache and performs complex operations to arrive at a final result. Cache updating is a bit of a pain and we are considering moving some of these processes to SQL Server queries that would just output the final result so that less data caching is needed at the c# app level. The processes are complex and we know that moving to the database would require extensive use of SQL Server CLR functions.

We see a lot of advantages in leveraging the database, but the required use of CLR functions gives pause for a few reasons:

No Azure: SQL CLR functions are not supported by Azure,

High Testing Cost: the SQL CLR functions could be slower and testing will take significant work

Small User Base: An hour of googling reveals that use of CLR functions is somewhat uncommon which makes community support (and possible MS support) a concern.

I would love to hear from someone who has moved a C# app from in process to CLR functions.

In your answers please assume that custom SQL CLR functions are required.

Answer 1

Your

• Compile
• Install
• Test
• Debug

Process is a lot harder with Sql Server CLR functions – I think you should automate as much of this as possible.

You may also need to get the agreement of a DBA every time you wished to update a function.

However after saying that “take your code to your data” can still be a very good option at times.