PASS Book Review by: Kathi Kellenberger
Rating:   (5 out of 5)

Overview

The “Inside SQL Server” series is the gold standard when it comes to books on SQL Server, and this book is no exception.  This book covers every aspect of T-SQL programming and focuses especially on performance and security.  Easy to run code samples illustrate every technique.  Many examples cover the differences between SQL Server 2000 and SQL Server 2005 code, which I found especially helpful. I recommend this book to anyone who wants to take his or her T-SQL skills to the next level.  I know I will refer to this book again and again.

Target Audience

This is not a beginner book.  The author recommends reading “Inside Microsoft SQL Server 2005: T-SQL Querying” or at least have extensive knowledge of T-SQL and the SQL Server 2005 T-SQL enhancements before reading this book.

Chapter Breakdown

Chapter 1:  Datatype-Related Problems, XML, and CLR UDTs

This was probably my least favorite chapter in the book.  In my opinion there is just too much covered in one chapter.  The main idea of the chapter is to explore the issues found by choosing one particular datatype over another.  But it also contains a long discussion, or proof, about how classes might map to relational database objects and an introduction to CLR user defined types.   The best part of the chapter for me was a code example about counting overlapping sessions that was very similar to a problem I had to solve at work a few months ago.  One of the interesting discussions in the chapter covered possible reasons to store XML data in a relational database. For example, it makes sense to store a Visio diagram as an XML document in an XML column instead of an image or binary(MAX) column because the XML document is then searchable. 

Chapter 2: Temporary Tables and Table Variables

Since the introduction of table variables, many myths have existed about when to use a table variable and when to use a temp table.  This chapter sets the record straight and includes a helpful summary comparing the use of temp tables, table variables and table expressions such as derived tables and common table expressions.  

Chapter 3: Cursors

I doubt there is a more controversial topic among T-SQL programmers than the use of cursors.  The chapter includes a great analogy comparing controversial constructs such as dynamic SQL and cursors to knives and matches.  While these objects can be very useful, they can also be very dangerous in the wrong hands.  The chapter illustrates some problems that can be solved with much better performance by using a cursor instead of a set-based solution.  If you have taken a college computer science course in analyzing algorithms you will find this chapter to be especially interesting as the author calculates the running times of different methods to solve the same problem.

Chapter 4: Dynamic SQL

There are even fewer reasons to use dynamic SQL than there are to use cursors, but they do have a place, especially with maintenance tasks.  This chapter should be required reading for every programmer and DBA as it really hammers home the risks of dynamic SQL.  Any time user input is used to build a query there is a risk of SQL injection.  This chapter gives advice on how to check the input and to use safer techniques such as using parameters with sp_executesql.

Chapter 5: Views

The reasons to use views include enhanced security and simpler queries.  This chapter covers the basics of views as well as indexed views and using views to update data.  Using Common Table Expressions within a view definition is one of the coolest reasons to use views with SQL Server 2005.

Chapter 6: User-Defined Functions

The most exciting news about user defined functions in SQL Server 2005 is the ability to write them with a .Net language for the CLR.  This chapter compares writing functions using T-SQL vs. the CLR for performance.  Not surprising, T-SQL is more efficient when it comes to data manipulation, and the CLR is more efficient for complex string manipulation and calculations. 

Chapter 7: Stored Procedures

Writing stored procedures is probably my favorite SQL Server task so I enjoyed discovering if I have been using good techniques all along.  The chapter covers different types of stored procedures, such as “special stored procedures” (those in Master that can be called from any database), reasons to use WITH RECOMPILE and the new OPTION(RECOMPILE) for individual statements, CLR stored procedures and much more.  A very cool bonus in this chapter is two stored procedures for writing dynamic pivot queries.  Why two?  The author shows the difference between a poorly written proc that works and a well written proc.

Chapter 8:  Triggers

I went from reading about my favorite topic to my least favorite topic when I turned to triggers.  Triggers can be very powerful, but like most powerful things they can also be dangerous.  New DDL triggers have been implemented in SQL Server 2005 that can be used to audit schema changes and changes to the databases or servers, or prevent the changes from happening.  And, of course, you can now use CLR triggers.  This chapter covers how to implement triggers as well as how to avoid problems with using them.

Chapter 9: Transactions

This is another chapter that is a must read for all DBAs and developers.  The chapter explains, along with sample code illustrating each point, transactions, locks, isolation levels, including the two types new to SQL Server 2005, and deadlocks and how to troubleshoot them.  I found the step-by-step explanations very easy to follow.  This can be a very dry topic, but the author makes it interesting.

Chapter 10:  Exception Handling

This chapter is an excellent introduction to using the new Try/Catch construct.  Very helpful is the explanation of using XACT_STATE to determine if the transaction can be completed.  I found the section on deadlocks and how to handle them with Try/Catch especially beneficial.

Chapter 11:  Service Broker

When I first saw this chapter I wondered why it was even in the book. I have to confess that I hadn’t taken the time to find out what Service Broker was all about before reading this chapter.  With Service Broker, you can easily create reliable database applications that are asynchronous using special T-SQL commands. For example, if several servers or systems are involved in a process, Service Broker guarantees that the updates will occur on all systems even if one system is temporarily unreachable or busy.  After learning the capabilities of Service Broker, I realized it could replace triggers that really do too much, like sending an email message, or even some SQL Agent jobs that monitor for certain situations.  The chapter provides a complete sample application in addition to the very thorough explanation of Service Broker.