One of the many features in SQL2005 is the ability to create stored procedures, functions (UDFs), and user-defined data types (UDTs) in any .NET language. As might be expected, most of the examples available are in C# and Visual Basic. However, I think that F# should get equal representation, especially since I think that certain features of F# can make it a great language for this:
- Object expressions to make it easier to re-use “boiler-plate” code for UDTs
- Easy IEnumerable functions for table-valued functions
- Compositional pickling for simpler binary serialization
- Integration with LINQ for more sophisticated query processing in UDFs
There are still some technical challenges that need to be addressed before F# can be used as a general purpose language with SQL2005 CLR. The biggest problem is that SQL2005 is very strict about the IL it allows to run since it runs “in-process” on the server and the classes and assemblies used can be shared between multiple users. Currently, F# runs afoul of one of the checks: all static fields must be marked as readonly. This restriction is to make sure that one user can’t change the contents of a shared class being used by other users. Don Syme is aware of the problem and working on a solution (no release date, yet).
However, with the release of 1.1.11.7, it is possible to start experimenting with F# and SQL2005 by disabling or bypassing the CLR security checks. By disabling the checks, we can use F#-generated assemblies at the risk of compromising the server. Therefore, I can’t recommend this in a production environment unless you have a very clear understanding of the security and stability implications and consequences of the various methods of disabling the checks. See the SQL2005 documentation for more details.
With these caveats, I’m going to present some sample F# programs which implement SQL2005 CLR objects. These can be run on any SQL2005 installion (including the free Express Edition). They require the use of .NET 2.0 (aka Whidbey) (another free download).
My plan is to have four more posts over the next week:
- Setup and a simple stored procedure
- User defined functions (both scalar- and table-valued)
- User defined data types
- User defined aggregate functions
I hope that this alternative look at F#/.NET interoperability will be interesting.