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 exceuted 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. 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 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.
Sample Project
We have a sample CLR Proc project page for more
detailed information and links to the current project download.