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:\SimpleMath1\SimpleMath1\bin\SimpleMath.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
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
which specifies where to find the function in this format:
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.