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