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: VistaDB.Compatibility.SqlServer.
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 is no security concerns for us.
When to use CLR Procs vs TSQL Procs
T-SQL Procs are collections of SQL statements that are executed in a batch as a function. They are best when you have little logic involved and are only wanting to somehow project the data into a new row structure. They are not very efficient at operations involving lots of logic operations.
CLR Procs and Functions are written in .Net and are much faster at executing complex logic chains, or when you need to take advantage of abilities not found in SQL. As an example if you want to take a string and convert it to another language through a webservice you could not do this in SQL.
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.
Your application can still be xcopy depolyed 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.
Building CLR Procs is a multi step process
Step 1 - Build your assembly to include the methods you want to call from SQL. Make sure your methods are configured correctly and have the proper attributes. See some example methods for CLR functions and procs.
Step 2 - Install your assembly into the database. This can be done through SQL or DDA. Once an assembly is loaded into the database it may also be updated from the external file.
Step 3 - Configure your methods as SQL functions, procedures or triggers in the database. The names of the methods in your assembly do not have to match those given when they are called. SQL or DDA may be used to configure the functions.
Step 4 - Call from your application using ADO.Net Commands, or SQL. There is no way to call CLR Procedures or Functions from DDA.
We have a sample CLR Proc project page for more detailed information and links to the current project download.