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
- 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.
©dkRanch.net September 2001
Return to Steve Jones Columnist Home