With SQL Server 2000's hitting its end of life date next April, many shops that have been delaying the upgrade to SQL Server 2005 need to start looking at it seriously. This is why we have chosen to review the seventh edition of William Vaughn's Hitchhiker's Guide to Visual Studio and SQL Server. Vaughn having written 12 books on SQL Server during his 14 year tenure at Microsoft and the years since then, he has the kind of historical knowledge that few writers on the topic posses.
In addition to the review, we have the privilege of offering a sample chapter to help you make your own decision. As always, comments from others who have already read the book are welcome.
Vaughn's book is a massive tome covering both the technical and philosophical aspects of developing database-driven software using Visual Studio and SQL Server.
Though a former Microsoft employee, Vaughn doesn't pull his punches. In his introduction, he is quick to point out the failings of both past and present Microsoft data access technologies. The section on how politics and marketing drove much of the transition from DAO through RDO and ADO and finally to ADO.NET really serves to clarify how we got to where we are.
The first chapter is on how to choose an architecture. Unlike other books that assume you are going to use the latest and greatest version of SQL Server, this one takes a moment to talk about customer expectations, developer skill sets, and what level of scalability is actually required. The sections often take the form of questions such as "What makes 'good', 'better', and 'best' designs?", "What's fast enough?", and "What about maintenance and administration?".
Be scalable within reasonable growth expectations. Not all applications need to support 10,000 (or even 1,000) users.
Another thing Vaughn warns against is trying to create database-neutral applications. He states that the theoretical ability to target multiple databases does not justify the loss of performance and flexibility that database-specific libraries and techniques offer.
Despite it being available only as a CTP at press time, Vaughn spends several pages covering the pros and cons of the lightweight database SQL Server Everywhere. He also promises an eBook on the topic later.
When talking about performance, Vaughn mentions how that DAO/Jet can be faster than AdO.NET and SQL Server in single user situations. Then he goes on to explain how SQL Server actually gets faster with more users due to caching. In the same philosophical tone that permeates this book, he gives weight to both the advantages of simple formats like Jet and at the same time considers the cost of transitioning from a single-user to a multi-user system.
Other topics covered in the 86 pages of the first chapter include connection management, server-side cursors, temporary tables, web vs. client/server development, and enough coverage of software engineering topics to put some Master's level courses to shame. The second chapter is on the inner workings of SQL Server. It starts with a comparison of the different editions of SQL Server 2005 and a rather hard to read chart.
Next, he goes into some of the problems that faced MSDE such as deployment issues and artificial performance limitations. For example, most end-users did not know they were running a version of SQL Server and thus had to install SQL Server service packs. After explaining how SQL Server Express Edition addresses these issues, Vaughn discusses some new problems such as the inability to publish replications and other features only available for the paid SKUs.
After touching on some more advanced issues like security, Vaughn drops all the way back down to the basics of creating SELECT statements. Those transitioning from other database engines may want to skim for differences, but really all but the most novice of developers will find it boring.
That is, until they happen across some of the lesser-known features of SQL Server like extended properties, which can be used to add metadata to most of the objects in SQL Server including tables, columns, indexes, and triggers.
Chapter 3 is a solid introduction to database design. It covers both the conceptual groundwork like normalization and implementation details like selecting ANSI versus Unicode. Chapters 4 and 5 cover the basics of using Visual Studio itself and using it to debug SQL Server stored procedures.
Chapter 6 covers datasets and related objects. Early on Vaughn seems to be skeptical about strongly typed datasets, especially when there is a chance that the underlying database schema may change. He also seems to dislike TableAdapters as well, but nonetheless he covers both topics in depth.
Strangely, web services are slipped into the end of chapter 6. While they are certainly simple enough to cover in such a short span of time, it is a rather curious place to put them.
Early in Chapter 7 Vaughn admonishes Microsoft for hiding and removing some data access features. He has instructions for restoring access to the deprecated data controls, but other features such as dragging a stored procedure onto a form to create a parameters collection simply do not exist anymore.
Other classes covered in this chapter include BindingSource and BindingNavigator. While these are covered in some depth, they are followed with a short and rather pointless couple of pages on the DataGridView. Keeping with tradition, he throws in a useful but somewhat random concluding section in this chapter. This time it is on the Progress Bar, including some thoughts on dealing with situations wherein the total run time of the operation is not known.
Chapter 8 provides a high-level overview of the ADO.NET classes themselves. It is not enough to really use the classes, but rather an introduction so you know what to look for later. Interspaced with the classes are interesting bits of trivia like the SqlDataReader being the first Microsoft data access object to expose a TDS stream directly. Previously it would have been loaded into a DataTable like object such as ADO's Recordset class.
Chapter 9, which is ostensibly about connection objects, starts with coverage security issues. Beginning with the basics that everyone needs to know, like how to expose SQL Server to the network, it then moves into some of the finer connection details that more advanced users care about. One such example is MARS or Multiple Active Record Sets. This .NET 2.0/SQL Server 2005 feature allows multiple operations to be performed at the same time using a single database connection. Vaughn recommends not using this technique for a number of reasons.
Another issue covered in this chapter is the use of just in time connections, always-open connections, and connection pools. While ADO.NET was designed with connection pools in mind, Vaughn covers how the other techniques can be used.
Vaughn really shines when talking not about ADO.NET itself but rather how to use it. His advice against returning SqlDataReaders from functions is bound to save many a developer the pain of tracking down connection leaks.
When discussing connection strings, Vaughn covers many of the details that other books gloss over or skip entirely. Unfortunately, he fails to cover the most important detail, how to protect the connection string from wayward users. Like so many authors, he says that it is important but shrugs when it comes to actually doing it.
Moving on, the Vaughn's coverage of connection pooling is excellent. A lot of time is spent on managing the connection, dealing with clustering issues, and how to handle situations that cause you to run out of connections.
The chapter concludes with a brief look at exception handling. Unfortunately it doesn't go into much detail and the advice is rather general.
The next chapter is on command objects. This one has a lot of good advice from the user's perception, such as how long a command can take before the user thinks the program is hung and forcibly kills it. Vaughn also covers some of the oddities found in the SqlCommand. For example, the fact that it has a Dispose method that does not actually do anything. (He doesn't mention that this prevents finalization, an regrettable side-effect of having Component as a parent class.) He also covers the Cancel method including the positive impact it can have on performance and the likelihood that it will cause problems with SQL Server when used incorrectly.
The discussion on using prepared command objects is useful. To summarize, it requires explicitly defined parameters, including size and type, and only makes sense if one is reusing the same SqlCommand object repeatedly. Later he covers techniques for holding onto a SqlCommand object for the lifetime of the application.
Skipping ahead to chapter 13, we see coverage of the CLR in SQL Server. For those of you unfamiliar with the concept, SQL Server 2005 allows developers to run .NET code in the database process itself. Not only does this include functions, users can even expose user defined types to be used as columns in tables. This chapter alone is reason enough to purchase this book.
Finally, a quick run-down of the remaining chapters. Chapter 14 covers SQL Server Reporting Services. The first appendix has instructions for setting up the sample database. The second appendix is a repeat of how to enable Visual Studio features hidden in the 2005 edition. The third appendix covers SQL Server monitoring, and really should have been a chapter in its own right. The fourth appendix shows how server-side cursors can be used in ADO.NET. While frowned upon by most DBAs, Vaughn seems to have a soft spot for them.
All in all this is a good book for both novice and experienced developers wishing to learn SQL Server and Visual Studio or to catch up on new features.