In-Depth

Yukon Tempts Database Developers

Long-awaited SQL Server 2005 offers Visual Studio 2005 developers more than just CLR integration. ADO.NET 2.0 opens the door to native data encryption, a new XML data type, and many added T-SQL features.

If SQL Server 2005 releases to manufacturing in the second half of 2005, this latest upgrade to Microsoft's flagship client/server database management system will have been more than five years in the making. According to Kimberly Tripp's DAT250 "SQL Server 2005: Bridging the Gap between Development and Administration" Tech•Ed breakout session, the SQL Server team's headcount has grown from about 400 for 1998's SQL Server 7.0 release to almost 1,000 for SQL Server 2005 and its belated WinFS project.

This article describes what VS database developers can expect from Microsoft's investment of 4,000+ person-years in SQL Server 2005. (4,000+ person-years assumes that the SQL Server 2000 team numbered 700 at release and grew at a constant 60-person/year increment to 1,000 over the past five years.)

It's a good bet that every Windows database front-end developer is at least aware of SQL Server 2005's most important new features and most VS 2002/2003 users have at least some hands-on SQL Server Project coding experience with VS 2005 beta and Community Technical Preview (CTP) versions. Paul Flessner provided an early view of Yukon during his October 21, 2001 presentation at the Professional Developer's Conference (see Resources). Flessner described Yukon's native HTTP Web services, XML data type, and XQuery processor, and he demonstrated C# stored procedure code hosted by the database engine.

SQL Server 2005's SQL/CLR features have undergone only superficial changes in the intervening four years, but VS 2005's latest SQL Server Project template greatly simplifies writing and deploying .NET stored procedures, triggers and user-defined functions, data types and aggregates. Another important .NET Framework 2.0 change is unification of ADO.NET 2.0's System.Data.SqlServer and System.Data.SqlClient namespaces for CLR code that runs inside or outside the SQL Server process.

ADO.NET 2.0 also lets developers implement Multiple Active Result Sets (MARS) with SQL Server 2005 and asynchronous SqlCommands with SQL Server 2000 or 2005. (You must specify a TCP/IP or named pipes connection to SQL Server 2000; a shared memory connection won't work.) MARS lets you execute multiple SqlDataReader objects on a single SqlConnection. MARS substitutes session pooling for connection pooling but, according to Microsoft's Angel Saenz-Badillos, doesn't improve data access performance compared with opening two pooled connections. The primary incentive for using MARS is to execute multiple commands in the same transaction isolation scope. The VS 2005 team dropped asynchronous SqlConnections shortly after announcing them.

Angel also strongly recommends against use of async SqlCommand operations with callbacks in Windows form apps, because ADO.NET 2.0 isn't thread-safe. SqlResultset objects, which implemented ADODB-style server-side cursors, the SqlCommand.ExecutePageReader and SqlCommand.ExecuteRow methods, and the SqlRecord class are missing in VS 2005 beta 2. .NET Framework 2.0's new System.Transactions namespace enables high-performance lightweight transactions that automatically escalate to distributed transactions when necessary.

T-SQL gets a raft of new reserved words, including PIVOT, UNPIVOT, CROSS APPLY, OUTER APPLY, and TRY/CATCH blocks for error handling. The PIVOT operator lets you write crosstab queries that are similar to Jet crosstabs but require fixed (explicit)—rather than expression-based—column headers. UNPIVOT returns PIVOT result sets to their relational source structure. The APPLY operator lets you invoke a table-valued function for each row returned by a query. Parameterized TOP n [PERCENT] queries accept variables of the bigint (float for PERCENT) data type as well as explicit numeric values. ROW_NUMBER, RANK, DENSE_RANK, and NTILE ranking functions return a ranking value for each row in a partition. A WITH CommonTableExpression clause generates a temporary, in-memory table object that enables recursive queries and disappears when the query completes execution.

DDL triggers can prevent dropping or altering database object or audit execution of DDL operations. T-SQL support for many other new T-SQL keywords are associated with DDL for new database objects, such as HTTP endpoints of SQL Server native Web services, Service Broker messages, encryption keys, passwords, or certificates, query notifications, and schemas for typed columns of the new XML data type. Another example is enabling SQL Server 2005's new Snapshot transaction isolation feature.

CREATE ENDPOINT lets you map one or more HTTP URL namespaces to the server instance. HTTP endpoints enable SQL Server 2005 running under Windows 2003 Server or Windows XP SP2 to register the URL with Http.sys, which listens for and receives requests on TCP port 80, 443 (SSL/TLS), or both. This feature lets developers create in-process SOAP Web services that resemble those produced by SQLXML 3.0 but don't involve IIS. Multiple Web methods you add with ALTER WEB METHOD statements execute stored procedures or user-defined functions that return SOAP response messages.

The native XML data type optimizes storage of XML documents or fragments for XML node indexing, which improves performance of XQuery expressions that return matching nodes or complete documents. Native, cell-level encryption minimizes potential civil or criminal liability for accidental or intentional disclosure of personal information—such as social security numbers or medical data. SQL Server 2005 encryption offers built-in certificate and key management, which simplifies implementing data protection scenarios despite the April CTP drop's almost complete lack of online help topics for encryption techniques.

SQL Server 7.0 added Online Analytical Processing (OLAP) services to version 6.5, and SQL Server 2000 provided data mining, notification and reporting, support for XML and many other important new features. According to Gartner's May 2005 "No Clear Winner in Overall RDBMS Market Share Race" research report (see Resources), SQL Server 2000 lead the RDBMS pack with an 18 percent overall growth rate from 2003 to 2004 and gained a 20 percent 2004 market share (compared to 34.1 percent for IBM and 33.7 percent for Oracle). 18 percent growth ranks as great performance in a market segment that Gartner found to have an overall growth rate of 10.3 percent.

Gartner attributes much of the 2004 database growth to vendors' business intelligence (BI) features, which are better classified in the database administration category. But new data encryption, XML data type, and Service Broker features are likely to generate a substantial part of SQL Server's market share growth from 2005 to 2006. In this case, market share, developer mind-share, and potential consulting revenue are likely to be tightly coupled.

About the Author

Roger Jennings is an independent XML Web services and database developer and writer. His latest books include "Special Edition Using Microsoft Office Access 2007" (QUE Books, 2007) and "Expert One-on-One Visual Basic 2005 Database Programming" (WROX/Wiley, 2005). He’s also a VSM contributing editor and online columnist and manages the OakLeaf Systems blog. Jennings’ Code of Federal Regulations Web services won Microsoft’s 2002 .NET Best Horizontal Solution Award. Reach him at Roger_Jennings@compuserve.com.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.