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:
- Run multiple operating systems simultaneously on a single PC
- 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, [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.
