Microsoft SQL Server Cubed

If you're like me and you enjoy having lots of books available covering SQL Server, you are certainly in luck. With the introduction of Microsoft SQL Server 2000, there have been a number of new books published on SQL Server.

Inside Microsoft SQL Server 2000, by Kalen Delaney, is the third edition of Microsoft Press's flagship book on SQL Server. This edition is written by Delaney, as was the second edition. Ron Soukup wrote the first edition. Delaney is a frequent contributor to many of the SQL Server newsgroups and has been working with SQL Server for quite some time. It shows in this edition.

The "Inside Microsoft SQL Server Series" has always been a bit unusual among SQL Server books in that it is not aimed specifically at programmers or administrators. This edition marks somewhat of a break from this tradition. In the Introduction, Delaney explicitly states that she has reworked the book to focus more on SQL Server internals, including the query processor and the storage engine. For the most part that is true. While you won't find many Transact-SQL-specific tips and tricks in the book, you will find implementation details that can help you optimize queries.

The book begins with a fairly detailed history of SQL Server. Microsoft SQL Server has a long and interesting history, and as with many tools, understanding the history can help you understand the current state of the product. After a tour of the system that includes a brief mention of the client programming interfaces, the book proceeds to Part Two, an overview of the architecture.

One of the best chapters is Chapter 4, which covers planning and installing SQL Server. While the installation for SQL Server has become easier to do, it has not necessarily become easier to do right. If you have more than a small installation of SQL Server, doing the hardware right is a large part of the battle. There are also chapters on tables, indexes, basic SQL statements, triggers, stored procedures, and the functions new to SQL Server 2000 functions. Coverage of cursors does not place enough emphasis on why they are used more than they are needed for my taste.

Part Four of the book is the one section that, in many ways, is not duplicated in other SQL Server books. When you have a question about locking or need some detail about how queries are processed, you will likely find the answer here. There is also good coverage on creating useful indexes.

Overall, the quality of the book is what one would expect from Microsoft Press. This does not seem to be a book with a chapter or two added and a new "SQL Server 2000" label applied. Coverage of SQL Server 2000 is integrated into the book, with the only specific section devoted to SQL Server 2000 changes in the first chapter's history of SQL Server. There were no blatant mistakes or typos, save an incorrect page number I discovered in the table of contents. The availability of the entire book on CD-ROM, completely searchable, is a bonus that cannot be overstated.

Like many books these days, you really can't judge a book by its cover. While some parts of Advanced Transact-SQL Programming for SQL Server 2000, by Itzik Ben-Gan and Tom Moreau, cover advanced topics, folks who are looking to go far beyond the normal Transact-SQL programming into areas that really stretch what SQL Server is capable of should perhaps look elsewhere (such as at Guru's Guide to Transact-SQL, by Ken Henderson, which I reviewed in the September 2000 issue of DDJ).

That said, this book does have a place on any beginning, intermediate, or advanced Transact-SQL programmer's bookshelf. There are portions of Advanced Transact-SQL Programming for SQL Server 2000 that teach you things you don't already know, and reinforce things you might already know. For example, in reading this book, I came across coverage of the SELECT INTO syntax. For whatever reason, although I have worked with SQL Server for years now, it had never hit me exactly how useful that functionality is. Advanced Transact-SQL for SQL Server 2000's coverage opened up SELECT INTO like no other book had. I mentioned this to another seasoned SQL Server programmer, and he said, "Well, yes, of course that is cool. Look at these stored procedures where I use it!" Every programmer has some blind spots. Looking through this book will help clear up any Transact-SQL programmer's blind spots.

One feature of the book that stands out is the SQL puzzle that appears at the end of each chapter. I generally don't care for this sort of thing and never actually type everything in (and either that or downloading the examples is required because the book does not come with a CD-ROM), but these puzzles were mostly well done and worth at least looking to the back of the book for the answers to see if your guess at the solution was the one the authors suggest. Additionally, the variety of ways that any given task can be performed is emphasized, with objective measurements used where appropriate. Screen shots of the graphical showplan from Query Analyzer enhance the discussions.

Advanced Transact-SQL for SQL Server 2000 begins with the coverage of Joins. Perhaps single file select statements are skipped because this is supposed to be an advanced text. The text continues with coverage of subqueries, data manipulation statements, and summarization techniques, including those that are not ANSI compliant but available in SQL Server. There are a couple of chapters devoted to things that a generic SQL book just can't cover; for instance, special data types and error handling. Views, stored procedures, triggers, user-defined functions, temporary tables, and horizontally partitioned views each have a chapter. These chapters include coverage of features new to SQL Server 2000, as well as some coverage of what Versions 6.5 and 7.0 had to offer, useful if you must work with multiple versions of SQL Server.

Finally, there is a chapter each on cursors, expanding hierarchies, and general tips and tricks. The cursor chapter begins with an amusing and instructive "Top 5 Reasons People Use Cursors." The authors' emphasis on the bad reasons people use cursors is followed up with a reasonably detailed explanation of how to use them in the best possible way when required.

Advanced Transact-SQL Programming for SQL Server 2000 does show some of the problems that can appear when multiple authors work on a book. There are places where transitions should be clearer and the prose is certainly not the best I have read, with spotty grammar here and there. Some of the examples have odd little problems, though nothing I tried was actually a show stopper. For example, one of the listings has the first name concatenated with a comma and the first character of the first name. Clearly the intent of the example was to list the last name, a comma, and the first name. This did not ruin the book-reading experience for me and I certainly do enjoy having this book available for reference. A searchable electronic version of the book on CD-ROM would be nice, as would having examples available without requiring a download from the Web.

Professional SQL Server 2000 Programming, by Robert Vieira, is another book where I think there was some title inflation. Thinking of a book entitled "Professional," I would presume that it is aimed at a more advanced reader than this book seems to be. Perhaps the fault is more that the book is tremendously comprehensive, weighing in at over 1300 pages! A book so long written by a single author is unusual. Further, the length is not arrived at by use of large fonts, superfluous illustrations, or other space-eating devices. In short, if you are looking to begin using SQL Server and have some experience with programming or databases, don't let the word "Professional" scare you away. The book does not assume that much prior SQL experience.

Overall, the style of the book is somewhat casual, and periodic notes in the text that might recall a particular experience of the author or something in particular to watch out for make the reading enjoyable. The initial chapters cover very basic topics, including the use of the tools that come with SQL Server. Once again, a professional will likely already have some knowledge of how to use these tools. Reading these chapters can clear up any blind spots you might have.

There are chapters on English query and full-text search, as well as performance tuning and an administration overview. The chapter on performance tuning is a good example of what makes this book stand out. While there is a right and a wrong way of doing many things, Vieira makes it clear that sometimes the wrong way might be required to actually get the job done. While discussing "Strategic De-Normalization," adding a column to a table in order to save an expensive join, he acknowledges what any seasoned SQL Programmer knows: There are times when all the relational design in the world will not solve a performance problem as well as a slightly denormalized table, where the denormalization is purposeful and well thought out. This is the kind of thinking that makes Professional SQL Server 2000 Programming worth the price of admission.

The production quality of the book is good, though if your eyes are as old as mine are, you may wish for a slightly larger font. Interestingly, the fonts for the listings of query results are quite large and readable. I would happily sacrifice readability of query results for slightly larger text for the book proper. Of course, larger text might mean a 1600-page book. While on the topic of length, again I would love to have a searchable version of the book on CD-ROM. More and more, I find myself buying books that I might not read cover to cover, but I really do want to have them available for researching a problem. Thankfully, the index of Professional SQL Server 2000 Programming is quite complete, and I have not had any real difficulties finding what I want.

So, if you are going out to get a new SQL Server 2000 book today, which one? That depends on who you are. If you are primarily using SQL through one of the cursor libraries, such as ADO or ODBC, and just need to know some administrative things, or be able to track down the periodic performance problem, then Inside Microsoft SQL Server 2000 is for you. Frankly, Inside Microsoft SQL Server 2000 might be a little too much book for you in this case, but if limited to a single book under those circumstances, this would be my choice. If, on the other hand, you are programming primarily in the SQL environment and you have support people to help you with SQL Server internals and administration, then Advanced Transact-SQL Programming for SQL Server 2000 might be the one for you. Finally, if you need to do it all, from administration to troubleshooting to SQL development, then get Professional SQL Server 2000 Programming, which can help you with all of these topics.

DDJ