ABCs of CLR Functions in SQL Server “Yukon”

You probably saw some of the news coming out of Microsoft’s recent
Professional Developers Conference. Microsoft showed off three major
products that will ship over the next two to three years:

• “Longhorn”, the next major version of Windows.
• “Whidbey”, the next version of Visual Studio .NET.
• “Yukon”, the next version of SQL Server.

Of course, all three of these products can work together. In particular, SQL
Server “Yukon” can execute code written with Visual Studio .NET “Whidbey”. But
how does that work? In this article, I’ll show you the basics of calling a
Whidbey function from Yukon, and help you think about where this capability
might come in handy.

A: Writing the Code

Yukon can execute four different types of .NET routines:

• Scalar-valued user-defined functions
• Table-valued user-defined functions
• User-defined procedures
• User-defined triggers

The simplest of these .NET routines is the scalar-valued user-defined
function. “Scalar-valued” means that it returns a single scalar type such as an
integer or a string. The function can have zero or more inputs. Such functions
are implemented in .NET as static methods of a class. For example, here’s some
VB .NET code for a class that exposes two functions that can be used as
scalar-valued user-defined functions:

Namespace SimpleMath
    Public Class Conversions
        Public Shared Function MilesToKm(ByVal Miles As Double) As Double
            MilesToKm = 1.609344 * Miles
        End Function

        Public Shared Function KmToMiles(ByVal Kilometers As Double) As Double
            KmToMiles = 0.621371192 * Kilometers
        End Function
    End Class
End Namespace

Remember, Visual Basic .NET uses the Shared keyword to indicate
a static method.

As a first step, then, I saved this code in a Visual Basic .NET class library
named SimpleMath. I set the assembly name to SimpleMath and the root namespace
to an empty string (you can make both of these settings in the Assembly section
of the project's property pages). Compiling this assembly gives me code that I
can call from SQL Server...after a bit more work.

B: Registering the Functions

The next step is to tell SQL Server that these functions exist, and that it's
OK to call them. SQL Server can't just execute arbitrary code in any old .NET
asembly. With the advent of Yukon, you'll discover T-SQL extensions to handle
this task. First, you need to register the assembly:

CREATE ASSEMBLY SimpleMath
FROM 'C:SimpleMath1SimpleMath1binSimpleMath.dll'

Next, you need to tell SQL Server about the particular functions within the
assembly. This requires one call to CREATE FUNCTION for each
function in the assembly:

CREATE FUNCTION MilesToKm(@Miles FLOAT)
RETURNS FLOAT
EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::MilesToKm
GO
CREATE FUNCTION KmToMiles(@Kilometers FLOAT)
RETURNS FLOAT
EXTERNAL NAME SimpleMath:[SimpleMath.Conversions]::KmToMiles
GO

The CREATE FUNCTION call may be familiar from SQL Server 2000,
where you can
use it to create a T-SQL function. The new syntax is the EXTERNAL
NAME clause,
which specifies where to find the function in this format:

AssemblyName:[Namespace.Class]::Method

The assembly name here, of course, is the one that was registered with the
CREATE ASSEMBLY call.

C: Calling the Functions

All that's left is to call the functions. This uses the same syntax that
already exists for calling user-defined functions in T-SQL:

SELECT dbo.MilesToKm(27.5), dbo.KmToMiles(42)

And here are the results:

---------------------- ----------------------
44.25696               26.097590064

(1 row(s) affected)

That's it! Although the internal plumbing to use CLR functions from T-SQL
must be quite complex, the SQL Server team has done a great job of making the
syntax easy to use.

So What Good is It?

Of course, implementing these simple conversion functions in VB .NET, while a
good demonstration of how to hook everything up, doesn't really make a lot of
sense. SQL Server is perfectly capable of doing multiplication itself, and
implementing these functions in T-SQL would involve less overhead. But there are
many things you can do with the .NET languages that are difficult or impossible
in T-SQL. Here are a few reasons that you might actually call .NET code from SQL
Server:

• To work with the event log, WMI objects, or other system resources
• To use the cryptographic functions built into the .NET Framework Class
  Library
• To perform complex mathematical calculations
• To move SQL Server data to Web pages or disk files

With the advent of .NET code in SQL Server, it's going to be possible to
perform a vast array of new tasks from within SQL Server applications. There's a
lot more to this capability than I've shown you here; in particular, I've
ignored both the well-designed security system and the ways in which .NET code
can call back into SQL Server to obtain more data. If you're a SQL Server
developer, you should definitely keep an eye on this stuff as "Yukon" progresses
from beta code to release.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD
70-306, and MCAD 70-310 Training Guides from Que Publishing. When he's not
writing code, Mike putters in the garden on his farm in eastern Washington
state.