Advanted T-SQL for SQL Server 2000 by Itzak Ben-Gan and Tom Moreau 5 Stars -

I glanced through this book in the bookstore one day and it looked like a good learning tool to improve my T-SQL code. I consider myself fairly advanced in this area, but after reading the "Gurus guide" I know I can improve. I used to recommend The Guru's Guide to Transact-SQL as the one book to get for T-SQL. This book is as good, perhaps, better than that one. You cannot go wrong with this book as a T-SQL reference.

From the beginning, I was not disappointed with this book. I opened this book and read the first chapter on Joins (a fairly simple topic), and then learned a few things. One thing I really like is the emphasis on SQL-92 standards. This is nice for a few reasons. One, it helps you prepare for other RDBMS, which any of us may find ourselves working with at any time. Second, SQL Server is moving in this direction and at some point (perhaps SQL 2003), support for the SQL-89 join syntax may disappear.

I found something useful in more every chapter. I made some notes about the content and helpful items I found. The book broken down into the following chapters:

• Joins in T-SQL - Inner, outer, and cross joins.
• Subqueries and Derived Tables - All types of subqueries covering almost everything you need to know. I especially like the comparison of different techniques from a performance point of view.
• Populating Tables - Using the INSERT statement and BULK COPY.
• Other Data Manipulation Issues - A large variety of different functions that can manipulate data. I have spent quite a bit of time in this chapter, especially in the dates section. I liked the methods that they presented for finding the beginning or end of the month.
• Summarizing Data - This chapter deals with grouping and the various grouping operators (CUBE, ROLLUP, etc.). While I do not use many of the items in this chapter, I did like the fact that the explanations are some of the most complete that I have seen on these options for grouping.
• Special Datatypes and Properties - A mix of items that don't really fit anywhere else. Identities, Text, sql_variant and table datatypes are covered here. This is really a SQL Server 2000 specific chapter as many items here are not available in other versions.
• Writing Code in T-SQL - Presents the two styles of the authors and provides some hints to writing more readable and maintainable code. This section also deals with SET and SELECT (and their differences) and transactions.
• Views - Covers almost everything that you would want to know about views.
• Stored Procedures - This chapter deals with stored procedures in every way. It covers how stored procedures are created and used, along with a nice section on performance. This is a well written section that covers most everything you need to know (and should know) about stored procedures.
• Triggers - Hidden Stored Procedures - A look at the complex world of SQL Server 2000 triggers. The changes implemented in SQL 2000 are covered in good detail here.
• User-Defined Functions - Not a great look at UDFs (I didn't see many useful functions), but this chapter does cover the system functions that exist along with how to create your own. There are a number of mathematical examples for functions, so if you need complex math, algebra, etc. in your application, this may be a good chapter for you.
• Temporary Tables - The first sentence of this chapter mentions that you should be able to solve most problems without temporary tables. That is the best introduction to this chapter. You should avoid temporary tables. That being said, this chapter does cover the behaviors and differences between local and global tempoary tables and their use. There are some good examples of where temporary tables can be used (like avoiding doing duplicate query work).
• Horizontally Partitioned Views - This feature of SQL Server is one of the great new features that helps SQL Server really scale. I had to learn how these worked for my book and I wish I had had this chapter to read and learn from. It covers the creation and implications of these views. Some great sections on how performance is affected by these views.
• Implementing RI and Cascading Actions - This chapter is mainly concerned with the implications of using triggers and the built in cascading actions in SQL Server. There is no real discussion of constraints, which was disappointing. This chapter does do a good job of setting up an example and using it to explain how cascading actions affect data.
• Server Side Cursors - Once again, the first sentence cautions against using cursors. Hopefully people will follow this advice and use cursors as a last resort. This chapter presents cursors in a terse fashion and then offers some alternatives to using cursors with other programming constructs.
• Expanding Hierarchies - The first chapter I have seen in a SQL book devoted to hierarchies. This structure was covered in every programming class I ever had, but rarely have I seen space or time devoted to this in SQL books. However, one thing I often model is a hierarchial relationship. I learned quite a bit about different ways to handle hierarchies, including a few I had not thought of myself.
• Tips and Tricks - A number of short SQL tricks or code tips that can solve some problems. A number of these tips have been used in articles on SQL Server Central (like insert..exec), and they seem to be the things that most people enjoy learning. A good chapter to read.
• SQL Puzzle Solutions - Every chapter closes out with a puzzle or problem of some sort that the reader is encouraged to solve. Most of these are not trivial and will require some hands-on-keyboard time to figure them out. Some of these were very difficult to solve, and it was nice to see how the author's solution compared to mine.

There were a few problems with this book. In the first chapter, the authors talk about the reasons to switch to SQL-92 standards. The first reason that is highlighted, however, is Reason #8. I couldn't find reasons 1-7, so this leads me to worry about the copy editing and it means I have to suspect and verify all the information I read. Despite the few problems I found, I found very few typos in this book. The code could be typed into Query Analyzer and it worked and returned the results that the authors intended.

This book is a fantastic reference for SQL Server 2000. Users of SQL Server 7 (or 6.5) will still learn things, but there are quite a few features that are only available in SQL Server 2000. If you do not have this book or The Guru's Guide to Transact-SQL, I highly recommend you pick up a copy of this book.

Steve Jones
©dkRanch.net September 2001

Return to Steve Jones Columnist Home