In "Back to SQL Server's Future" (April 2005, InstantDoc ID 45309), I wrote that data is at the core of most modern computer systems and SQL Server 2005 is set to blur the traditional boundaries of the n-tier model. A key driver that's changing the way we think about tiers in computer architecture is the inclusion of the Common Language Runtime (CLR) in SQL Server 2005. The SQL Server CLR is one of the most talked about features of SQL Server 2005, and it draws strong opinions from all camps—from those who want to turn it off forever to those who want to use it for everything. People's reactions to the integration of the CLR into SQL Server 2005 are interesting. One member of the SQL Server development team was giving a presentation about CLR integration when a member of the audience stood up and called out, "Is T-SQL dead?" This question prompted us to build an entire session called "No... T-SQL Isn't Dead!" (I'll cover the enhancements to T-SQL in SQL Server 2005 in a future column.) In contrast, I had a conversation with an ASP programmer who had just found out about SQL Server CLR integration and immediately decided that this would now be a great place for him to store all of the .NET assemblies for his Web site.
When you're thinking about the SQL Server CLR, it is important to understand when it is and isn't appropriate to utilize it. The Microsoft article "Using CLR Integration in SQL Server 2005" (http://msdn.microsoft.com/sql/2005/2005articles/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp) explains in detail how to choose when to use the SQL Server CLR. Judicious use of the CLR can solve many problems that you currently solve by using middle-tier code. In essence, you can think about the SQL Server CLR as a tool that gives you the ability to move complex procedural logic that works primarily against values stored in the database from the middle or client tiers to the server. The CLR lets you take advantage of thousands of useful functions and classes that exist in the .NET Framework API. The ability to use standard managed code can sometimes significantly ease the creation of these complex functions, thanks in part to the many rich constructs—such as arrays and lists—that .NET languages provide but T-SQL doesn't support. These functions can also provide significantly better performance because managed code is precompiled, whereas T-SQL is an interpreted language. In fact, in cases that require complex computation, conditional logic, string manipulation or the like, managed code can outperform T-SQL by an order of magnitude, with fewer network roundtrips than data processing in the middle or client tiers require.
The main question that most people have about the SQL Server CLR is, "Where should my code live?" The SQL Server CLR gives you the ability to write almost any middle-tier business code as data-tier objects—but when is this a good idea? To decide, you need to consider what the code is doing. At Tech ED 2004, I spoke to a software architect who had just been to a SQL Server CLR presentation. He enthused, "This is great! Now I can actually put the right things in my data tier." If your middle-tier code makes heavy use of data—especially if it's manipulating data and putting it back in the database—that code should sit on the SQL Server CLR. For example, credit-card processing is an operation that relies heavily on the person data in the server. The operation uses the data, then interacts with an external credit-card agency to confirm a credit-card transaction. Most of the work is data based, so it's cumbersome to pull all of the personal data off the server and into the middle tier only to send it to an external agency and return a yes or no answer. The integration of the CLR into the SQL Server Engine lets you do all this processing on the server without moving any data.
Another effective use of the SQL Server CLR is to replace most, if not all, of the existing extended stored procedures in many SQL Server installations. The SQL Server CLR can provide security, reliability, and performance benefits that extended stored procedures can't. There are a few processor-intensive operations for which native code in extended stored procedures is still beneficial, but in most cases, you'll want to rewrite existing extended stored procedures as SQL Server CLR functions. I saw a string-manipulation demonstration that illustrated the benefits of making this change. The presenter took an address string stored in a single text field and split it based on a delimiting character. The demonstration showed the code required to perform this operation in Visual Basic .NET and in T-SQL; the Visual Basic .NET implementation required one line of code, whereas the T-SQL version was more than a page long. Also, when the presenter executed the code, the Visual Basic .NET code in the SQL Server CLR ran about 40 times faster than the T-SQL implementation.
Finally, the SQL Server CLR gives developers the ability to extend the database by creating their own, complex user-defined types (UDTs) for SQL Server. One developer I talked to said that the greatest thing the CLR brings to SQL Server is that he can now create a highly performant UDT that converts BIN to HEX, and he can run it in the server, close to the data. This ability removes the need to transfer large quantities of data across the network to perform the conversion in the middle tier, then move the result back into the server. UDTs implemented with the SQL Server CLR also give you the power to extend the database to meet your specific needs. For example, if you need a specific data type that SQL Server doesn't support, you can use a custom UDT to implement the type—an easier option than performing middle-tier conversions of a standard SQL type. Another example is the ability to use UDTs to model the data types of other database vendors. If you work in a heterogeneous environment and have to deal with multiple databases on various database servers, the ability to create types in your SQL Server that directly map to types that other servers support could greatly improve the interoperability of all the systems.
The discussion of whether the SQL Server CLR is good or bad is certainly not over. But if used appropriately, the addition of CLR functionality to SQL Server 2005 can yield many rewards and will lead to a fundamental change in the way we architect and build database systems. Next month, I'll look at the inclusion of XML in SQL Server 2005 and discuss how it helps close the gap between the database and applications.
