Q&A with Itzik Ben-Gan
T-SQL Querying and Query Tuning Enhancements in SQL Server 2005-2012
Q: If your pre-con had a theme song, what would it be and why?
A: “All You Need Is SQL”: All you need is SQL, all you need is SQL. / All you need is SQL, SQL, SQL is all you need. / All you need is SQL (all together now). /All you need is SQL (everybody). / All you need is SQL, SQL, SQL is all you need.
As for why…, you don’t need a reason why all you need is SQL – you need a reason why not!
Q: What excites you most about today’s T-SQL?
A: That almost every day I learn something new about it. For example, the other week I realized that I had never written a recursive query that had multiple recursive members (multiple queries in the CTE body with a recursive reference to the CTE name). So I started looking for use cases and found very interesting ones. Some were practical use cases, but the one I enjoyed most was a query that produced the following output:
That’s just beautiful.
Q: Where does your workshop take attendees beyond what you might cover in a 60- or 75-minute session?
A: In a short session that covers querying and query tuning, you can either cover one topic thoroughly or several small tips. In a whole day about querying and query tuning, you can cover multiple important topics thoroughly and connect them to one bigger theme.
Q: What's the most surprising statement attendees might hear you say during the pre-con?
A: I don’t want to spoil the surprise by giving away the whole statement, but I can tell you that it starts with SELECT…
Q: What’s the biggest myth you’d like to debunk about tuning T-SQL queries?
A: One of the myths that I’d like to debunk concerns the declarative nature of SQL. You can express your request in multiple different declarative ways that ultimately have the same meaning and produce the same result. Some people believe in the theory that “never mind how you express your request – as long as it means the same thing, the optimizer will generate the same plan.” And for this reason, your focus in tuning should be more around aspects like indexing and not how to write the code. This may be the case in theory, but you know what Einstein said about theory and practice: "In theory, theory and practice are the same. In practice, they are not."
Often, different queries get different plans, even when they mean the same thing and return the same result. Part of becoming good at query tuning is gaining knowledge and experience with how the optimizer tends to optimize certain query constructs, and then when you aim at a certain kind of plan, you use certain query constructs. It’s true that in a way this idea contradicts the "physical data independence" principal from the relational model, but alas, that’s practice.
Q: What still surprises you in your adventures with the APPLY operator or ROW_NUMBER function?
A: I’m so glad you mentioned APPLY and ROW_NUMBER because these are two of my favorite T-SQL features. I keep discovering new and exciting ways to use them all the time. What amazes me the most is how simple both tools are and in how many creative ways they can be used to solve very complex problems.
But what probably surprises me most is how especially the APPLY operator is underutilized. Often when I teach about T-SQL, I ask students, “Who is using APPLY?” And to my surprise, very few hands are raised, if any at all. This is one of those features that once you get to know it, you cannot stop using it. In the pre-con, I’ll show a number of creative ways that APPLY can be used.
Q: If attendees could start putting into practice just one new T-SQL feature after your pre-con, what would you want that to be?
A: I’m sure that attendees will put not just one, but a number of features into practice after the pre-con because so many of them are so useful. For sure, they will apply APPLY – in more ways than one!
Check out our other Q&As with PASS Summit 2012 pre-con speakers.