Leveraging the CLR's Power

In "The CLR's Inclusion in SQL Server 2005" (http://www.windowsitpro.com/article/articleid/45445/45445.html), I began a discussion about why Microsoft engineered SQL Server 2005 to host the Windows .NET Framework 2.0. Microsoft made this change to let database developers leverage the power of the .NET Framework in their databases and to do so with greater security than they've had using tools such as extended stored procedures.

Because the .NET Common Language Runtime (CLR) provides a managed runtime environment, database developers can integrate computationally intense processing without having to open Pandora's box of calls to external resources. The transfer of control beyond SQL Server's limits is a big area of concern when using extended stored procedures. And it's why SQL Server 2005 introduces the three Code Access Security (CAS) levels for CLR functions. With the CLR and CAS, you can allow complex computations to occur within the database in an efficient manner instead of facing the tradeoff between inefficient processing on the database server and returning an interim set of data, which you must then manipulate off the database server.

The CLR in SQL Server 2005 isn't meant to replace but rather complement T-SQL. Although you can create a .NET stored procedure that's equivalent to a T-SQL stored procedure, this isn't how you should leverage the CLR's power. In fact, in the article "Using CLR Integration in SQL Server 2005," Microsoft has already stated that T-SQL is the preferred tool for querying data. Keep in mind that Microsoft enhanced T-SQL in SQL Server 2005. (My favorite enhancement is the ability to handle recursive queries. This ability is great for a hierarchical data model in which a parent node is used to trigger a series of queries in an ever-deepening tree of child records. The end result is a single recordset that contains the entire tree.)

Although T-SQL is the preferred query language, the CLR takes the performance lead when it comes to processing data. This is especially true when parsing complex data, such as data stored in or passed to a database. For example, suppose you want to retrieve a subset of an embedded XML column and return the various embedded elements as unique rows. The CLR lets you process this XML column and turn it into a set of rows, which a larger query can then use as part of a return. The CLR also takes a performance lead when aggregating complex data. For example, suppose you want to aggregate several different columns, some of which might be from different sources or dependent on related columns in a table. Using the CLR, you can process these conditionals in a much simpler fashion as part of a function called by your original query.

Another example is the typical order-order details scenario in which you have data for an order and an unlimited number of rows that represent the order's details. The standard approach is to store the rows with a foreign key to the order. However, ADO doesn't let you pass the set of rows as a table to SQL Server. Thus, you need to make a roundtrip to the base order table, returning the new key, after which you have to make a roundtrip to the database server for each of the detail rows. With the CLR, you can use a different approach. Instead of passing a table, you can pass an XML structure as one of the parameters to your stored procedure. Within that XML structure, you place each row you want to insert. Now you can use the CLR to query that XML structure and with a single roundtrip, your query can insert not only the new order but also all the order details.

These examples show how you can leverage the CLR to enhance performance. However, the CLR plays another important role. In SQL Server 2005, Microsoft updated the capabilities of user-defined types (UDTs) so that UDTs truly extend the base scalar types. The updated UDT capabilities allow you to go beyond simply storing aggregated data in a custom XML structure. Instead, you can create a custom type for a single value. These updated UDT capabilities require the use of the CLR because, to define a custom type, you first need to create a custom class with the .NET Framework. This class can then define specific storage and data formatting capabilities associated with your new type.

Overall, having the CLR integrated with SQL Server 2005 provides you with a new set of tools to improve the performance of your database applications. To leverage this powerful set of tools, you need to keep your approach simple, take advantage of the CLR functionality that lets you create new functions, and use XML to extend your database. For a more complete discussion of how and when to leverage the CLR in SQL Server 2005, I recommend that you read "Using CLR Integration in SQL Server 2005," which you can find at the following URL: http://msdn.microsoft.com/sql/2005/2005articles/default.aspx?pull=/library/en-us/dnsql90/html/sqlclrguidance.asp#sqlclrguid_topic13.