Editor's note: This article is based on the beta 1 release of Yukon. Some items covered in this article might change before the product's release.
One of the most-anticipated yet least-understood features of the upcoming Yukon release of SQL Server is its integration of the Common Language Runtime (CLR). This is an extremely rich topic, covering many new features and functions for database administrators and developers. As you probably know by now, Yukon is based on the Microsoft .NET Framework and provides new features that go beyond the capabilities of SQL Server 2000.
CLR Integration
The CLR is the engine of the .NET Framework, facilitating the execution of all managed application code. With Yukon, managed code is part of the core application-development technologies. Managed code is code that's compiled for the .NET environment. It runs completely inside the .NET Framework, without relying on other resources such as COM components (we discuss these later). The CLR translates managed code to executable code, which the CPU then executes. The .NET Framework also provides class libraries—key software building blocks of .NET applications.
All code in .NET belongs to a unit called an assembly. An assembly contains two important types of data: code compiled to Intermediate Language (IL) and metadata. IL is a form of assembly code that the CLR can translate to executable code at runtime. This process is called Just-In-Time (JIT) compilation. The server retains the executed code in memory until the host process terminates. The metadata that the compiler generates describes the assembly's individual classes and the assembly unit itself. Figure 1, page 24, outlines this process. By leveraging integration with the CLR, Yukon supports the development of managed code and its deployment to the database.
The integrated CLR will let developers use .NET languages in addition to T-SQL to create database objects including stored procedures, user-defined functions (UDFs), and triggers. CLR integration also lets developers build user-defined data types (UDTs).
Yukon uses an implementation of ADO.NET for the data access that CLR code performs in the database. This implementation's classes, such as SqlDataReader and SqlCommand, provide a familiar programming model for ADO.NET developers. And when you're writing a managed-code application to run in the SQL Server process, you already have an implicit database connection, so the SqlConnection object doesn't need initializing. This implementation makes in-process data access simple, yet it's powerful enough for developing rich applications.
The ability to run managed code in Yukon doesn't mean that T-SQL is going away. In fact, Yukon has significant T-SQL enhancements. (See Itzik Ben-Gan's article "What's New in Yukon T-SQL," page 27, for more about T-SQL enhancements in Yukon.) However, a .NET language can be useful in some situations. For example, CLR-based code might be a better choice for computation and logic-intensive operations because of the optimization you get from running compiled code against the CLR. A .NET language compiler compiles the CLR code into a binary executable format, but the SQL Server database engine always interprets T-SQL at runtime. In addition, .NET languages such as C# and Visual Basic .NET provide better looping and branching constructs than T-SQL does. Writing structured code is easier with these languages than with T-SQL, and having access to the .NET Framework class libraries opens up new possibilities that don't exist with T-SQL, including graphic image manipulation, advanced string processing, and advanced arithmetic computations.
In SQL Server 6.5 and later, developers have two basic options for implementing code in their databases. Some use the OLE automation for stored procedures (sp_OA) series of procedures to create and use COM objects from SQL Server. The Windows OS uses COM objects heavily, leveraging a programming infrastructure that lets you encapsulate and define a piece of code so that many programs can use it. Before .NET, COM was the primary mechanism for developing Windows-based applications that require distributable units of code. For example, a Visual Basic (VB) 6.0 project to build a class module compiles as a COM object, which lets other programs use it as long as they can implement a COM component. The sp_OA feature lets developers make limited use of COM code inside the database. The drawbacks of sp_OA include cumbersome deployment and limited support for debugging these objects while they're running within SQL Server. Yukon's support for managed- code applications addresses some of these limitations.
In addition to sp_OA, SQL Server developers can use extended stored procedures—stored procedures that are written in C or C++ and compiled as DLLs. Developers often use extended stored procedures to perform computation-intensive tasks or to access resources outside the database. But extended stored procedures are also difficult to write and are notorious for causing memory-related problems and unhandled exceptions in code.
With Yukon's CLR integration, developers can use .NET languages to build the functions that they've previously used sp_OA and extended stored procedures for. The CLR has advantages over these other options because of its built-in security model, the stability and safety associated with executing code in a managed environment, and the ease of development, debugging, and deployment. In addition, access to the .NET Framework class libraries can make programming with the CLR far more productive than using sp_OA or extended stored procedures. Let's look at an example that shows the power of using managed code in the database.
Prev. page
[1]
2
next page 
