A Developer's Guide to SQL Server 2005

Bob Beauchemin

Wow. I can't believe it's been almost two years since I was writing the preface for our first book, A First Look at SQL Server 2005 for Developers. Those two years, and the two years before, have been a whirlwind of working with the SQL Server product, including teaching, consulting, writing, and conferences. I still haven't stopped to catch my breath. SQL Server 2005 was officially launched in September 2005. And we've been working on enhancements to the First Look book since it was published in mid-2004. As with any book on a beta version of a product, the product itself changed after the book was published. In fact, one of the first changes was a feature cut that came two weeks after the book shipped. Keeping up has been a high-energy endeavor, because since beta 2, there were an order of magnitude more additions and enhancements than cuts. This book is the product of the great response we got to the First Look book and the answer to the frequently asked question, "When are you guys going to do an update?" I'd say that this is quite a bit more than an update, however. We've included lots of new and revised material; there are too many changes to enumerate. There is even a section on best practices because at this point in time, the early adopters who shipped revised versions of their software that works with SQL Server 2005 have already had a few years to practice.

We did, however, wait for the "golden bits" to appear before embarking on the last of the series of revisions. This turned out to be a good move, as the implementation details of a few of the features changed slightly up until the last candidate release. This is a Sisyphus-like strategy for the most part, however, as the software keeps improving. As I write this, Service Pack 1 has been announced and may indeed change things slightly. Work has begun on the next release of SQL Server, code-named Katamai, and the next version of the .NET Framework is currently available in beta. Software products are never static. Dan and I will be including the code samples from the book and updates on our Web sites:

http://www.SQLskills.com (Books section)
http://www.pluralsight.com

We'll also be writing about SQL Server 2005 features and enhancements in our blogs.

The ANSI SQL 2003 specification "shipped," too, and included a slightly different version of Part 14: XML Related Specifications (SQL/XML). The W3C XQuery committee is closing in on becoming a recommendation. As an additional point of note, since the First Look book was published, both of Microsoft's biggest competitors in the database space have released .NET Framework procedure inclusion (although integration is not as tight) and have announced or released XQuery support.

By now, you probably have heard about the .NET Framework-based procedures, the inclusion of the XML data type and XQuery language, the enhancements to T-SQL to support robust error handling and queries against hierarchical data and open-schema based designs, and versioningbased transaction isolation. These features, however, combined with inclusion of an asynchronous messaging system, SQL Server Service Broker, permit SQL Server to move toward a Service Oriented Database Architecture. Event Notifications, Query Notifications, and database mail are the first manifestations of this type of architecture; it moves SQL Server toward being more of a system that responds asynchronously to external events, rather than being strictly connection-and-statement-based. Service Broker, combined with CLR procedural code and XML data support, enables database programmers to build their own service-based applications. The inclusion of support for Web Services as an alternate client stack is another manifestation of this architecture. This is such a compelling programming model that Microsoft plans to include similar functionality as part of the

next series of .NET Framework enhancements. When this framework functionality is integrated with Service Broker's robust transport and built-in transactional and database support, it should make for a powerful combination. This architecture is a quantum change because it enables building scalable, robust, distributed database applications by designing distribution of processing in from the ground up.

It's still my conclusion that there's quite a bit in SQL Server 2005 for just about every developer, DBA, application designer, business analyst, and data miner. I'm still reading in some places (though not as many as in 2002) that the new features just aren't that interesting; they're more like a recitation of glitzy acronyms than substance. So I'll end this with the same mid1981 personal history lesson.

I'm working for an insurance company in Seattle, and we're planning to convert our indexed file data, which we'd just converted from ISAM (indexed sequential access method) to VSAM (virtual storage access method), to a better, more robust database engine. The one we had in mind was IMS (IBM's Information Management System product). The salesperson, however, wants us to look at some newfangled database called SQL/DS (which eventually became DB2). After designing some tables and playing around with some queries, we asked some tough questions like "Why does it matter that a database engine is built on a mathematical theory?" and "Why would you want to learn a foreign query language called SQL rather than using nice, fast assembly language or COBOL programs?" and "Why did you just decompose our 2 nice, understandable records into 30 little tables just to join them back together again?" and "Why does it go so slow?" It was the beginning of the relational era. Relational engines weren't all that optimized yet, and smart programmers with custom navigation-based code could beat the engine every time.

In 1981, we sent the product back, and I didn't learn SQL until 1987. By then, I was a bit behind on the learning curve, but relational engines were a heck of a lot faster, and programmers wrote a little SQL and much less procedural and navigational code. And they got much more work done. So I smile when I see folks shake their heads about the XML data models or the XQuery Formal Semantics. I saw the same raised eyebrows when mixing service-oriented concepts and data first came on the scene. Maybe the

head-shakers are right, but I'm not waiting until 2010 to learn XQuery or how to integrate messaging and databases. It doesn't matter whether you choose to wait, however, or use relational exclusively. SQL Server 2005 and .NET Framework 2.0 have the enabling engines for all these data storage, messaging, and query technologies. And the future portends even more work in these areas.

Portland, Oregon March 2006

Dan Sullivan

This journey started well over two years ago, when Bob asked me whether I would be interested in joining him in creating a course and book about the "new" version of SQL Server that Microsoft was working on. He said that this would be the biggest release of SQL Server to date and that it would make both XML and the Common Language Runtime first-class citizens of Microsoft's database platform. That sounded good to me, so here I am writing a preface for a book about the shipping version of SQL Server 2005.

One of the first things I realized when I started digging into it was that XML and the CLR integration are just the tip of the iceberg. Just the enhancements to T-SQL are worth upgrading a server. The enhancements in ADO.NET, such as SqlDependency, which keeps an application and a database in sync, are in tune with the needs of applications, and having the same programming model on the client and server made life much simpler, too.

Direct support of XML as a first-class data type in the database gives you the choice of shredding XML data into tables, storing it natively, or even doing some of each based on the needs of the application, not the limitations of the platform.

Likewise, the direct integration of the CLR into the SQL Server runtime gives you a choice of how to implement an operation based on the needs of the application, not the limitations of the platform.

To carry XML and CLR further as first-class citizens, SQL Server 2005 hosts Web Services. Again, this lets you choose how an application connects to SQL Server 2005 based on applications' needs. Applications that connect to a Web Service don't need ADO.NET, ADO, OLE DB, or any other library to make the connections.

SQL Server 2005 opens your choices and widens its applicability to business problems.

But my favorite comes last: Service Broker, the framework for implementing business transactions. The first time I looked at Service Broker, I did a flashback to projects I had worked on in the past and was bowled over by it. It is right on target. All those busywork details I had to code just to move a few things around to match a business process or control resources are now done for me by Service Broker. Service Broker truly is the best thing since sliced bread.

Taken as whole or in pieces, SQL Server 2005 is a huge step forward. Working on the book and explaining to developers as it was changing underneath my feet was a challenge, but well worth it. I couldn't have met that challenge, of course, without the patience, understanding, and love of my wife, Alberta.

Bolton, Massachusetts March 2006