Intro to CLR Stored Procedures and CLR Functions
CLR Stored Procedures and Functions are a relatively new way to build extensions for your database. Traditionally stored procedure logic has been written in SQL, but SQL Server 2005 introduced the ability to use CLR code for procedures. Microsoft sometimes calls this SQL CLR as the technology used to load CLR assemblies into SQL Server. Prior to SQL CLR, you could only extend SQL Server using C++ DLLs that were difficult to build and maintain.
We implemented SQL CLR assemblies very early in the VistaDB 3 development cycle to allow users to extend their databases using the same language they wrote their application (C# or VB.NET). During the upgrade to VistaDB 4, we had identified a number of small changes we wanted to make to CLR Procs to make them more compatible with SQL Server, and to make it possible to build an assembly that would work with both VistaDB and SQL Server with only a recompile (no major code changes). We have achieved that goal in VistaDB 4 through the addition of a new namespace:
High Level Overview
CLR Procedures and CLR Functions are both just
public static methods in an assembly. The entire assembly is loaded into the database and the procedure runs internal to the engine.
Note that any external dependencies to your assembly will NOT be loaded into the database. SQL Server does this, but we do not as it leads to complex issues around the Digital Copyright act that we don't want to get involved in.
Your application can still be xcopy deployed without needing the assembly that contains the methods. This is very useful for deployment, but can be confusing during development. Just because you rebuilt the DLL does not mean the database has the most recent version. After each compile, you must update the assembly in the database in order to load it.
Typical Uses of CLR Procs
Need special math functions not supported by SQL? Want to handle a storage type not handled by SQL? Want to write a trigger to do things beyond what a traditional SQL Trigger can accomplish? Want to lookup a domain name in DNS?
Use a CLR Proc to handle things like specialized encryption, archive, remote connections, specialized business logic, pull an RSS feed, request data from another database, authenticate a user against a domain, I think you get the idea. Anything that you can do in managed code can be done in a CLR Proc.
CLR procs are still viewed as somewhat suspicious among a lot of DBAs. I think this is mostly because they can't see what the function is going to do, so there is some fear of losing control. CLR integration has to be enabled by the admin on SQL Server for it to work. VistaDB always supports CLR Procs because we live within your application, there are no security concerns for us.
We are working on new pages for the website and help covering SQL CLR Procs and Functions in VistaDB 4. I expect this to be a multi part series that will include pages here on the blog that are mostly similar to the help and website. But a lot more people read this than actually read the help (sad really).
See the CLR Procs tutorials page for more information.