Data Management

Application Development: SQL Server "Yukon" and CLR integration

Yukon, code name for the next version of SQL Server, will include several feature enhancements designed to make integration with the .NET Framework more efficient. One way this integration will be achieved is through the Common Language Runtime module.


If you're a Microsoft programmer, you have probably invested quite a lot in the transition from Windows NT/SQL Server 7 to Windows 2000/SQL Server 2000. The transition to Longhorn/Yukon is going to require a similar investment. Enhancements to SQL Server (Yukon) will include more complex reporting services and better handling of XML/XSL. The big new feature is integration with the .NET Framework.

A lot of us like to think of Yukon as "SQL Server .NET."

SQL.NET?
In Yukon, one of the most bragged about features is the ability to embed .NET code in SQL. There are already all kinds of procedural code features in SQL, but integration with the .NET Framework will, in theory, allow for even more powerful code features like the ability:
  • To open, close and communicate via TCP/IP and UDP ports.
  • To access the cryptography and OS integration classes.
  • To interact with COM objects via .NET wrappers.
  • To do anything else possible with .NET.

Author's note
If you even think about using .NET code embedded within Yukon SQL to wrap and manipulate COM objects, you should already be a master's master of all of those technologies.

Modular and reusable
If you're used to calling SQL code that is composed dynamically through a scripting or compiled language, the concept of that SQL code itself containing further scripting or compiled code can be a bit mindbending. That's why out-of-the-box, OOP programmers often talk about "wrapping their mind around" a problem. A benefit of this encapsulation within encapsulation is that it makes the code modular and reusable.

Use-case scenario
Let's suppose you have a Web site where you accept credit card payments. You have a component that accepts a credit card number, expiration date, and signature code, and returns a true/false validation. Currently your Web page programmers must first run the validation process before calling the SQL Server 2000 stored procedure that records the transaction.

Using .NET code embedded within Yukon SQL, your Web page programmers could skip the validation process and simply call the stored procedure. The SQL within the stored procedure could use .NET code to call the validation component, and return a pass/fail value with the stored procedure results. If you decide at some point to change the component in a significant way, you would only need to recode the stored procedure and not every single Web page that references it.

Sample SQL syntax
Getting past the reasons for using .NET code embedded within SQL—let's take a look at how the actual code would look. Here's an example of some pseudo-C# code, embedded within an SQL Server Yukon Stored Procedure:
CREATE PROCEDURE cmdMyProcedure (
    @username NVARCHAR(4000)
    )
AS EXTERNAL NAME
    YukonCLR:[MyNETNamespace.MyNetCode] :: myfunction


This creates a stored procedure that takes a "username" parameter. The stored procedure, when called, launches the "my" code to return (or not) a result.

You want to be very careful when you perform this kind of near-circular referential programming. There are all kinds of checks and balances in the suite of .NET technologies to keep you from writing fatal code, but you can still do it. If you create an assembly that, upon initialization executes a stored procedure, and that stored procedure in turn creates another instance of the assembly and initializes it—well, then the circle is complete and the CPU will be able to heat Ice Station Zebra as it continuously loops.

To avoid this kind of melt-down, you could make sure to wrap any classes which will be used by Yukon with a flag, warning that such a situation is occurring. Of course, Yukon is still in beta, so a similar safety feature may be included in the final release.

CLR integration
SQL Server Yukon achieves the ability to execute .NET code through the CLR (Common Language Runtime). This is nominally equivalent to the JVM (Java Virtual Machine) against which Java programmers program. The ability to call compiled code from SQL is shared by Oracle/Java developers, although not nearly as efficiently.

The CLR integrates so much better with Yukon than Java does with Oracle because Microsoft is integrating SQL Server development into the Visual Studio IDE, producing a common design environment for source code and SQL. Sun and Oracle are separate companies with separate agendas, so getting a similar level of integration out of that platform combination is not likely in the near future.

Benefits of integration
Building great programming languages in the 80s, and great IDEs in the 90s is where Microsoft's real strength has always been. (It certainly didn't get rich building bullet-proof operating systems.). During the 00s, it is betting that revenue stream on initiatives that embrace all kinds of programming languages through Visual Studio .NET, including SQL.

By adding SQL to Visual Studio, and thereby adding CLR to SQL Server, the integration between the two is complete. .NET applications can more efficiently communicate with back-end database servers, and those database servers can more efficiently manage remote business logic that would otherwise eat up database or front-end CPU cycles and memory.

Editor's Picks

Free Newsletters, In your Inbox