Home » Uncategorized » Currently Reading:

Review: Inside MS SQL Server 2008: T-SQL Querying

September 29, 2009 Uncategorized 1 Comment

It’s day two of Banned Book Week / the MidnightDBA book review party, and we're digging into one HEAVY SQL tome: Inside Microsoft SQL Server 2008: T-SQL Querying, by Itzik Ben-Gan, with Lubor Kollar, Dejan Sarka, Steve Kass, and Kalen Delaney.

I’m a big fan of Itzik Ben-Gan’s 2005 book of the same name, so I’ve been waiting for this updated version since slightly after I heard they were going to call the new SQL Server “Katmai”.

The bottom line, right here at the top, is that this is an absolute gold mine for T-SQL developers, both as a study guide and as a mark-it, underline-it, dogear-it reference. The book is aimed at experienced developers, but if you’re just starting out, get it anyway, along with the T-SQL Fundamentals book. (As of this writing, both books are on sale new at Amazon.com for $31.49, an excellent bargain on the usually $49.99 books.) 

Let’s take a quick trip round the chapters, shall we?

Chapter 1 discusses logical query processing, which is not necessarily the same as the physical processing.  In other words, the way the SQL engine actually processes queries is more efficient than it might seem from the logical querying. I love-love-loved this in the 2005 book, and love (x3) it here.

Chapter 2 is new: Set Theory and Predicate Logic, by Steve Kass.  As he says, “The more you understand about set theory and logic, the easier SQL will be for you.”  This chapter sets out in words the things I understood instinctively about databases and logic.  In fact, this chapter says most of the things I want to say when I talk about breaking down a SQL problem, so ignore me when I blog about this and just read the T-SQL Querying instead.Steve begins by introducing new concepts along with an example, then digs down into definitions and conventions. 

Now, I have to admit it: Set Theory was the bane of my existence in college (I failed it twice, seriously).  But I’m at the point now where I am ready to take the next step in my education, and I want this as my guide starting out.As a side note, I find a very comfortable sort of amusement, almost nostalgia, in the Mathematical Conventions discussion:

Everyone now and then in my beginning programming classes, a student – usually a good one – will name variables with an extra dose of creativity, and I’ll be confronted with something I call the penguin dialect of programming…

For(int penguin = 0; penguin < tiger-1; ++penguin) {

For(int Betty = 0; Betty < tiger-penguin-1; ++Betty) {

IF (abba[Betty+1] < abba[Betty] { …

Yes, yes, I have seen such code. 

Chapter 3 and 5 are new: respectively, The Relational Model, and Algorithms and Complexity. The relational model chapter covers basic definitions, data integrity, and normal forms, as you’d expect, but also has a great section on relational algebra and calculus. Again, these are the tools to use when we’re ferreting out the needed set of data, and Itzik presents them here clearly and concisely.  And Chapter 5 (also by Steve Kass) gives us a look into the mind of the SQL engine, including a section on sorting algorithms that somehow also take me right back to high school comp sci II.

Chapter 4, Query Tuning. This chapter, for me, is one of the (or just The) major selling points of the book. At 150 pages – enough for a book in its own right – chapter 4 is the go-to reference for tuning methodology (developed by Solid Quality Mentors), tools, examples, and enlightenment.

Chapter 6, Subqueries, Table Expressions, and Ranking Functions – I can’t praise the section on subqueries highly enough.  The CTE and OVER/PARTITION BY sections are well dog-eared in my copy, too.

Chapter 7, Joins and Set Operations – Exactly what you think, only better and more in depth.  Come to think of it, that could’ve been the entire book review.This review is starting to grow a bit more than I expected – though really, you can’t review 800 pages in under 250 words – and I was amused to find that I’m trailing off right about the time the chapter play-by-play trails off in the book’s introduction.  “Chapter 1, 2, 3, 4, 5, yadda yadda yadda”, we seem to say… 

Chapter 8, Aggregating and Pivoting Data – Totally inspired the Grouping Sets video.

Chapter 9, TOP and APPLY

Chapter 10, Data Modification

Chapter 11, Querying Partitioned Tables – Totally new chapter, by Lubor Kollar, who "led the development of partitioned tables and indexes when first inroduced in the product". I love educational material written by the people who created the product…

Chapter 12, Graphs, Trees, Hierarchies, and Recursive Queries

Oh, and Appendix A is one of the coolest things I’ve seen in a tech book: A set of logic puzzles!

One final word: This is a big book, and that can be intimidating. But it shouldn’t be, and you shouldn’t try to take the book in big bites. See our video on How to Study, and you and SQL Server 2008: T-SQL Querying will live happily ever after.

 -Jen McCown, http://www.MidnightDBA.com

Currently there is "1 comment" on this Article:

  1. [...] a year ago, I reviewed Itzik Ben-Gan’s book “Inside MS SQL Server 2008: T-SQL Querying” (I called it “an absolute gold mine for T-SQL developers, both as a study guide and as a [...]

Comment on this Article:

Release Date: Minion by MidnightDBA

Just 1 day 5 hours 9 minutes 1 second until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category


How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!


The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/