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.

Develop, test, and deploy the most complex networked server-class applications running on Microsoft(R) Windows(R), Linux(R) or Novell(R) NetWare(R) all on a single desktop

Spend less time configuring and rebooting, more time developing

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, bills@interknowlogy.com

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

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

4. New and Improved

(by Karen Bemowski, products@sqlmag.com)

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 us-sales@altova.com. http://www.altova.com

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

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More