One of the most innovative features in the upcoming SQL Server 2005 release, formerly code-named Yukon, is Common Language Runtime (CLR)-based code execution. If you're a developer or your job combines the duties of developer and DBA, you probably know about the CLR. But if you're a DBA who doesn't work with the development side of SQL Server, you might have tuned out the CLR, assuming it won't affect you. If you haven't been paying attention to the CLR, I have some advice for you: The roles of all people who work with SQL Server are changing, and you need to learn this new technology whether you call yourself a DBA, a developer, or a programmer.
In SQL Server 2000 and earlier releases of the database platform, you coded SQL Server procedures, triggers, functions, and other objects in T-SQL. In SQL Server 2005, you can also create these objects in C# and Visual Basic .NET, and Microsoft plans to continue adding .NET Framework CLR languages to SQL Server. Microsoft even wrote significant parts of SQL Server 2005 in .NET languages such as managed C++, C#, and Visual Basic .NET. In general, SQL Server 2005 exposes .NET Framework functionality by letting you
- write SQL Server executable code such as stored procedures and triggers in CLR-based C# or Visual Basic .NET and invoke those stored procedures the same way you do T-SQL stored procedures
- write CLR-based functions in C# or Visual Basic .NET and invoke them the same way you do T-SQL functions
- write CLR-based user-defined data types (UDTs) and aggregates and use them and their associated code to define your tables and business rules
In SQL Server 2000 and earlier releases, you can call external code by using extended stored procedures, which call a precompiled DLL coded to the Open Data Services (ODS) API specification. You use these extended stored procedures to perform complex mathematical calculations, expose functionality in the OS, perform CPU-intensive operations, or just provide functionality that T-SQL can't perform without assistance. One problem with using extended stored procedures is that SQL Server must fully trust the code. Because you use unmanaged code to create extended stored procedures and they run in the same address space as SQL Server, a problem in an extended stored procedure could harm SQL Server—in fact, it could blue-screen the system. Not good. CLR-based code execution means SQL Server developers and DBAs can further and safely extend the power of their procedures.
To see the power of CLR-based code execution, let's look at how I created and deployed a semi-complex CLR assembly project designed to perform a common task: capture and encrypt credit card information. I used the bits of Visual Studio 2005 (formerly code-named Whidbey) and SQL Server 2005 that Microsoft released at its Professional Developers Conference (PDC) in October 2003, although you can create all my examples by using the current release of Visual Studio .NET. The pre-beta versions of Visual Studio 2005 don't integrate with SQL Server 2005 yet, but Microsoft plans to reintegrate them this summer. You'll need to construct the code and deploy the assembly DLLs to SQL Server 2005 by using T-SQL scripts, as SQL Server Books Online (BOL) explains.
A word of caution: Remember that the bits I use are alpha code. Thus, big hunks of it work, but Microsoft development teams are still ironing out the details and interfaces of the final product. It's also not fair to evaluate performance based on this build.
Understanding the CLR
The .NET Framework, which Figure 1 illustrates, is built on a language-neutral CLR architecture that lets developers use virtually any computer language to construct .NET applications. Think of the language as the human-to-computer interface—there are as many languages as there are types of programs and programmers. At this point, Microsoft expects SQL Server 2005 to support three languages: T-SQL, C# .NET, and Visual Basic .NET. The stored procedures you're using in SQL Server 2000 and earlier releases should continue to run, and you'll be able to create new stored procedures, functions, and UDTs in any of the three supported languages. Unlike T-SQL, the .NET languages can draw on any of the more than 5000 classes that the .NET Framework supports.
Figure 2 illustrates how the CLR works. Visual Studio launches the CLR compiler, which takes a block of code called an assembly and generates a DLL that contains an intermediate language (IL) form of the code. The CLR converts the code to native machine code and executes it.
I recently met with a group of Microsoft Regional Directors and magazine editors to discuss upcoming Microsoft technology. The group agreed that the CLR is exciting but that users need to understand it better. Many of us expressed the fear that developers might assume that CLR-based stored procedures would be faster, better, cooler, and otherwise superior to traditional T-SQL stored procedures. Sometimes, folks tend to oversimplify the viability and suitability of CLR-based executables. For example, I read an article that said, "A .NET stored procedure is no different in usage than a regular SQL stored procedure." Such broad statements extolling the virtues of CLR-based executables are dangerous. My concern is that developers might try to use CLR-based code to do something simple such as convert Fahrenheit to Celsius, and when they discover that the new code runs slower than the T-SQL it's replacing, they'll abandon the CLR. Let's look at the technology more closely and try to ground ourselves in reality.
Prev. page
[1]
2
3
next page 
