An Argument for Managed Functions

To be honest, I’m having a hard time resolving when you would actually want to run managed code in SQL Server. This might be a reflection of my newness to the concept, but in general is seems there are limited, very specialized cases (like complex math or financial functions) where you would need it. Then it hit me: you can share utility functions with your applications.

Here is the scenario: you have a business rules utility library that has a bunch of static methods for validating data. Said library is chuck full of regular expressions you downloaded from web sites (where people really know how to write them) and you’ve wrapped them all with friendly names like IsSocialSecurityNumber() and IsDate(). The later even accounts for leap year and is a complete mystery to you.

Now you’re tasked with importing data from external source and you’ve decided the simplest way to do this is straight into SQL server. You’d like to save yourself a ton of effort by leveraging those regular expressions and now you can.

Compile your code using visual studio into a class library called ValidationLibray.dll and move it to a deployment drop point that SQL Server can see.

Next, create the assembly in SQL Server and create new SQL Functions to call your external ones.

Now you can leverage your managed code in SQL as well as your .Net apps and have one codebase for simple validations. Pretty cool.

I did notice that SQL is much pickier about what code it will allow to run. For instance, if you are a fan of the lazy load pattern for your classes (like I am), you’ll be disappointed that SQL considers this code “Unsafe”. So I ended up re-writing my class (even to the extent of marking private properties readonly) so it would load into SQL Server.

Another thing I noticed right off is that while I’m used to using Boolean types in C# (bool) there is no Boolean in SQL Server. I got lucky and used bit for the return type of my function. SQL is doing some implicit type conversion for me. It’s more common for me to use Int for my Boolean functions in SQL, but bit is the same as int (bit 1 = int 1) so no big deal there.

I hope to find more good reasons to incorporate the new CLR support in SQL Server, but I’m still skeptical and worry about performance.