Developer .NET UPDATE--Leveraging the CLR's Power--February 18, 2005

This Issue Sponsored By
This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for Developer .NET UPDATE.

VMware Workstation 4.5
http://www.vmware.com/wl/offer/1294/0

SQL Anywhere Studio from iAnywhere Solutions
http://crm.sybase.com/sybase/www/iAS/sqlany_developer_q105.jsp

Free ASP.NET 2.0 Training CD!
http://www.appdev.com/promo/RN00121

1. Developer .NET Perspectives

2. Events and Resources

3. Announcements

4. New and Improved

Sponsor: VMware Workstation 4.5
Join the Virtual Revolution--try VMware(R) Workstation now, free! With award-winning VMware Workstation 4.5, you can:

VMware Workstation is powerful virtual machine software for developers and system administrators who want to revolutionize software development, testing and deployment in their enterprise. Essential features include: virtual networking, live snapshots, drag and drop, shared folders and PXE support, making VMware Workstation the most powerful and indispensable tool for enterprise IT developers and system administrators. Get your free trial now!
http://www.vmware.com/wl/offer/1294/0

1. Developer .NET Perspectives

by Bill Sheldon, [email protected]

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

Sponsor: SQL Anywhere Studio from iAnywhere Solutions
8 million deployed seats. 1,000 application partners. 20,000 developers. 1,000 customers. FREE for developers. We know it sounds too good to be true, but it isn't. With low maintenance requirements, high performance, open tools and technology integration, and a low cost of ownership, SQL Anywhere Studio is trusted by leading application developers worldwide as the database powering their single- and multi-user desktop, client server, remote office and mobile applications. The free Developer Edition has all the features of SQL Anywhere and doesn't expire at any time. Download today at
http://crm.sybase.com/sybase/www/iAS/sqlany_developer_q105.jsp

2. Events and Resources

(brought to you by SQL Server Magazine)

New Web Seminar - SQL Server Administration for Oracle DBAs
Sign up now for this FREE, one-hour Web seminar and get a quick start in mapping Oracle database-management skills, knowledge, and experience to SQL Server database management. Learn about the varying similarities and differences between Oracle and SQL Server and get a preview of real-world tips and techniques for managing these associated technologies. Register now:
http://www.windowsitpro.com/seminars/sqlserveroracledba/index.cfm/index.cfm?code=0216emailannc

SQL Server Magazine Connections Conference
March 20-23, Orlando, FL. Over 45 sessions presented by Microsoft and industry gurus. Registration includes an exclusive day of presentations on SQL Server 2005 by the Microsoft SQL Server 2005 product team. All attendees receive the latest SQL Server 2005 and Visual Studio 2005 beta. Call 800-438-6720 or 203-268-3204.
http://www.sqlconnections.com

Discover All You Need to Know About 64-Bit Computing in the Enterprise
In this free Web seminar, industry guru Michael Otey explores the need for 64-bit computing and looks at the type of applications that can make the best use of it. He'll explain why the most important factor in the 64-bit platform is increased memory. Discover the best platform for high performance and learn how you can successfully differentiate, migrate, and manage between 32-bit and 64-bit technology. Register now!
http://www.windowsitpro.com/seminars/integrityservers/index.cfm?code=0216emailannc

3. Announcements

(brought to you by SQL Server Magazine)

Nominate Yourself or a Friend in the MCP Hall of Fame
You are a valuable and active member of the MCP community, so why shouldn't you get the fame you deserve? Nominate yourself or a peer to become a part of the first ever MCP Hall of Fame. You could win a VIP trip to Microsoft and other prizes. Enter now!
http://www.windowsitpro.com/mcphalloffame/index.cfm?code=0216emailannc

Introducing the SQL Server Magazine Monthly Pass
Sign up now and get the "golden key" to the SQL Server Magazine Web site. Here's your opportunity to get immediate online access to every article ever published in the magazine – that's thousands of how-to articles, expert tips and helpful blogs to use whenever you need it. Sign up today:
http://www.windowsitpro.com/sub/MonthlyAccess/index.cfm?promocode=ep2152dp

Hot Spot: Free ASP.NET 2.0 Training CD!
See why AppDev was voted the best by thousands of developers nationwide...get a FREE ASP.NET 2.0 Training CD-ROM -- a $95 value! Click the link below for your free training CD...
http://www.appdev.com/promo/RN00121

4. New and Improved

(by Karen Bemowski, [email protected])

DiffDog Tracks Down Differences in Development Projects
Altova released DiffDog 2005, an easy-to-use synchronization tool that facilitates the comparison and merging of files, folders, and directories for application developers and power users. Intelligent syntax-coloring, line numbering, indentation guides, folding margins, and other innovative features help developers compare source code and XML files. The tool integrates with any version control system that supports external differencing applications. DiffDog 2005 comes in Standard and Professional editions. The Professional Edition provides advanced XML-aware differencing and editing capabilities. Pricing for a single-user license starts at $69 for the Standard Edition and $129 for the Professional Edition. A free 30-day trial is available. For more information, contact Altova at 978-816-1600 or [email protected].
http://www.altova.com

Contact Us

Developer .NET UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/rd.cfm?code=00ep214xeb

View the SQL Server Magazine Privacy Policy
http://www.winnetmag.com/aboutus/index.cfm?action=privacy

SQL Server Magazine is a division of Penton Media, Inc. 221 East 29th Street, Loveland, CO 80538, Attention: Customer Service Department

Copyright 2005, Penton Media, Inc. All Rights Reserved.