Before I start this review I must disclose that I do work with the author of the book, Kathi Kellenberger. She is the DBA and I’m a Senior Applications Analyst, working primarily with SQL. With that being said, after reading the book and finding it very useful I felt the need to write a review to share with others looking for a T-SQL book to improve their skills.
I have worked with T-SQL for about three years now and consider myself as having intermediate T-SQL knowledge. I’ve written basic queries, worked with Stored Procedures, and functions to name a few of the features. However, since reading this book, I’ve found there are many features available that I have not utilized such as Common Table Expressions (CTEs), Error Handling and Constraints. Exercises are given at the end of each topic to help the reader apply what they’ve learned and the solutions are provided at the end of the book. This is an excellent book for not only the T-SQL beginner, but also the intermediate T-SQL programmer.
Chapter 1 starts off explaining how to install SQL 2008 and gives an overview of SQL Server Management Studio (SSMS). One thing to note in this chapter is the author doesn’t go straight into query writing, she first explains the basic database concepts such as data types and the new schema security model introduced in SQL 2005.
Chapter 2 goes into the actual query writing process using the basic select statement and filtering data using the WHERE clause and COMPARISON operators. The not equal to (!=) operator is also covered in this chapter along with the IS NULL operator. I’ve had queries in the past where I haven’t accounted for NULLs and inaccurate results have been returned. In this chapter, the author explains how to account for NULLS and also provides sample queries along with the results for a better understanding.
Chapter 3 is an introduction to string manipulation. This was one of my biggest challenges when I first started writing queries, getting a certain string from a particular field. Some of the functions covered in this chapter are REVERSE, DATEADD, DATEDIFF, and CASE. Beginners of T-SQL will find the REVERSE function is a necessity when working with strings. For instance, I’ve found it very useful when I need to get the position of the backslash (\) while working with strings. DATEADD and DATEDIFF, along with other functions that manipulate the date are also covered. This chapter provides clear-cut examples of how to use these functions.
Chapter 4 discusses how to get data from two or more tables by using JOINS and SUBQUERIES. One of the most common tasks of T-SQL programmers is gathering data from one or more tables. In this chapter, the author does a good job of explaining the different types of JOINS and also provides good examples that illustrate the use of JOINS along with the results.
One thing to note in this chapter is although the author explains JOINS she warns the reader of the problems that can be caused with incorrect joins that result in the CARTESIAN PRODUCT, the result of running a query in an infinite loop. So not only does this chapter cover the correct way to write JOINS with the results, the author also has samples of incorrect JOINS and the incorrect results that can be returned, or not returned.
Also in this chapter, some of the functions introduced in SQL 2005 are discussed, COMMON TABLE EXPRESSIONS (CTEs)and how they compare to DERIVED TABLES. This chapter breaks down CTEs in a manner that’s easy to understand and provides examples that clearly illustrate the point. I must admit, I haven’t used CTEs, I’ve continued to use temp tables to hold data or simply have a stored procedure with a lot of joins that can be somewhat confusing. After reading this chapter, I now see that the code is easier to read and understand when using CTEs. This chapter explains CTE’s on a basic level that’s easy for beginners as well as intermediate programmers to understand; the need for temp tables can be limited with CTEs.
Chapter 5 covers Grouping andSummarizing data. A variety of T-SQL operators are covered in this chapter such as COUNT, SUM, and AVG. The author lists some o the most commonly used operators along with the definition of each one. The examples that are provided build on functions learned in the previous chapters. For instance, the COMMON TABLE EXPRESSION and JOINS are used along with the OPERATORS in the examples.
When I first started writing AGGREGATE queries, one of the hardest concepts for me was which field had to be included in the GROUP BY section. The reader will find that this chapter has a detailed explanation of what should be included in the GROUP BY section as well as sample queries and exercises that help gain a better understanding of the GROUP BY function.
Chapter 6 teaches data manipulation using the UPDATE, INSERT, and DELETE statements. This chapter is a must read for T-SQL beginners, it covers a variety of ways to manipulate data using some of the functions covered in previous chapters, such as AGGREGATE queries and EXPRESSIONS.
The different ways to delete data from a table are discussed, this chapter covers both techniques and provides queries and results of both. As with previous chapters, performance is discussed and the author walks the reader through an exercise using different queries so the reader can see first-hand how queries that modify data can impact performance.
Chapter 7 deals with logic using the WHILE, IF, IF-ELSE statements along with ERROR HANDLING. The basic IF statement is covered and examples are provided to show the different ways to write the statement. This chapter then goes into more complexity by discussing how to write multiple IF statements. What I find useful in this chapter is the author provides different scenarios as to why you might want to write IF statements then provides the syntax along with the results for the statement.
ERROR HANDLING is also discussed, the chapter covers how to trap errors the traditional way using the error variable and also a new method which was introduced in 2005. Having an error message displayed which can help troubleshoot the issue has always been a time saver for me. This chapter goes into detail with error trapping and as with previous chapters, has exercises to help the reader apply what they’ve learned.
The controversial CURSOR is also discussed in this chapter but the author points out that it should be used with caution and shows how a poorly written CURSOR can impact SQL performance.
Chapter 8 discusses CONSTRAINTS, PRIMARY KEYS, and FOREIGN KEYS. Although PRIMARY KEYS have been discussed earlier in the book, this chapter goes into more specifics, such as characteristics of a PRIMARY KEY, clustered and non-clustered PRIMARY KEYS, along with the syntax. The author provides sample queries that attempt to alter a table with PRIMARY KEYS and CONSTRAINTS in place so the reader can see what happens when constraints are violated.
Another topic in this chapter that I have to mention is STORED PROCEDURES. Some of the common mistakes made by developers using STORED PROCEDURES to return data is discussed.
FINALLY, TRIGGERS are discussed but the author cautions that they must be written efficiently and when not they can impact performance. A number of other LOGIC expressions are listed in this chapter that explain how to pass variables and return data.
Chapter 9 covers more advanced T-SQL techniques that the beginner will not need right away but the author covers these techniques so the reader is familiar with them. The new data types supported in SQL 2005 and 2008, the VARCHAR(MAX)and NVARCHAR(MAX) data types are two that are discussed. This chapter explains how to use these new data types and the author does a good job of demonstrating how large strings are handled using these data types.
Earlier version of SQL stored references to the file on disk or in the IMAGE data type, this chapter explains the new feature FILESTREAM which has the benefits of both methods. Other new data types are also discussed in this chapter, those that handle hierarchal data and the new SPATIAL data type.
Chapter 10 also covers advanced techniques such as using multiple CTEs in one query, RECURSIVE queries, MERGE and the PIVOT function. Other 2005 enhancements are also covered in this chapter. Even the more experienced T-SQL programmer can review the material covered in this chapter and find it useful, different scenarios are covered and then the syntax and query are provided along with the results.
It is clear from the material covered in this book that the author is very knowledgeable in T-SQL and if the reader takes the time to read each chapter, review the samples and perform the exercises in this book they are well on their way to beginning a proficient T-SQL programmer. The solutions to all exercises are at the very end of the book so this book can also be used as a reference for a long time to come.
You can buy this book from Amazon today!