Itzik Ben-Gan would doubtless agree. He chanced upon a string of incomprehensible characters that he found so fascinating it launched his career. A happy accident many database professionals have been thanking him for ever since.

An exceptional author who interweaves the heavy SQL (and Transact) Server stuff with nuggets of whimsy and anecdote, he first heard of SQL while on a course in the Israeli army. He fell for it immediately and after his military service was over was involved in networking, programming, databases and data warehouses.

I also love Guinness,
so when abroad I
usually try to
find a pub that
serves Guinness
and has
wireless connectivity.
That’s the ideal
combo as far as
I’m concerned.

An in demand teacher, mentor and inspirational speaker, Itzik has appeared at numerous training events around the world including TechEd, DevWeek, SQLPASS, SQL Server Magazine Connections. His books include Microsoft SQL Server 2008: T-SQL Fundamentals, Inside Microsoft SQL Server 2008: T-SQL Querying and Inside Microsoft SQL Server 2008. The author of numerous articles for SQL Server Magazine as well as white papers for MSDN.

In 2002, he founded Solid Quality Mentors, the training and mentoring company that has spread itself around the globe and which includes 40 winners of the Microsoft Most Valuable Professional (MVP) award. A winner of the MVP award himself, Itzik lives with his wife Lilach and a number of Israeli Canaan dogs.

RM:

The release date of SQL Server 2008 R2 (aka Kilimanjaro) is expected to be mid-May next year.  What do you hope it will include and what you areas do you hope have been left out? While we’re on a similar subject what are your hopes for SQL Server 11?

IBG:

I’m afraid that as far as I know not much was added in SQL Server 2008 R2 in the relational side like T-SQL, which is my focus. And at this point of the development cycle I don’t think that any unplanned features can be added. As for SQL Server 11; I, as well as many others, would love to see more effort spent on T-SQL. Specifically, I do hope to finally see more effort spent on window calculations (the ones that use an OVER clause). This is by far the single most powerful and profound concept that exists in standard SQL. SQL Server 2005 introduced partial support for those. SQL Server 2008 unfortunately added nothing in this area. I do hope that SQL Server 11 will add the missing parts.

There are many other important standard SQL features that are still missing in T-SQL that I’d love to see in SQL Server 11, but more complete support for window calculation is by far the most important hands down.

RM:

Why is that?

IBG:

For one, window calculations allow you to phrase things in an intuitive manner-similar to the way you think of them in your mind. For many people SQL is not intuitive because it requires you to think in terms of sets. Sets should be considered as a whole, and sets have no order to their elements. For many people coming from procedural programming background the intuitive way to think of data processing is one record at a time, and in some order. The transition to set based thinking can take a long time. The design of the windowing concept is ingenious in the sense that it allows you to phrase things closer to the way you think without breaking any concepts of the relational model that stem from mathematical set theory. Where relevant, you can define logical ordering in the window in order to frame the window rows. That’s logical ordering for the purposes of the calculation only, as opposed to forcing order in the data, like when using a cursor. As an example, in order to calculate a running total quantity for each employee and day using a window calculation, you would use the following query:

1 2 3 4 5 6

SELECT empid, dt, qty,   SUM(qty) OVER(PARTITION BY empid                 ORDER BY dt                 ROWS BETWEEN UNBOUNDED PRECEDING                          AND CURRENT ROW) AS sumqty FROM dbo.Sales;

A table is a relation; a relation is a set; a set has no order. Therefore, forcing order in the data would constitute a fundamental violation of the relational model. Conversely, defining logical ordering as part of the specification of the calculation has no contradiction with the model-the input of the query is a set, and the output is a set as well. That’s an ingenious insight that someone had when coming up with this idea of window calculations.

Also, window calculations lend themselves to good optimization. That’s especially true in the “fast track” case like in the above example, where the frame is “ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,” meaning all rows until the current. Having an index on the key list (empid, dt) and included list (qty), it should be an obvious thing for the optimizer to handle this query with a single ordered scan of the index. Such a plan would have linear algorithmic complexity, meaning that if you increase the number of rows by a factor of f, the run time would also increase by a factor of f. The set-based approaches to handle this problem that are currently available in SQL Server are not optimized that well. Take the following solution, for example:

1 2 3 4 5 6 7

SELECT S1.empid, S1.dt, S1.qty,   SUM(S2.qty) AS sumqty FROM dbo.Sales AS S1   JOIN dbo.Sales AS S2     ON S2.empid = S1.empid    AND S2.dt <= S1.dt GROUP BY S1.empid, S1.dt, S1.qty;

First, I find it less intuitive than the window calculation. Second, given the aforementioned index is in place, the plan for this query involves per each row from the table scanning all rows that have the same empid value and a smaller than or equal to dt value. Meaning that in respect to the partition size, the complexity of this plan is quadratic. Increase the partition size by a factor of f, and run time will increase by close to f2.

In short, window calculations allow you to phrase calculations in an intuitive manner, and they also lend themselves to good optimization. 

RM:

I have the feeling that you enjoy detail. Are you more comfortable with numbers than words?

 

IBG:

I’m tempted to answer with just a “Yes” or just a “1,” but I’ll resist the temptation. I would say that I’m more comfortable with SQL code than with words. It’s pretty hard for me to express my true intentions with words, and I always have to think a lot about how to say things such that my true intentions will be conveyed, so that I won’t hurt or offend people by mistake, and so on. With SQL it’s different-it’s much more natural to me.

RM:

Grant Fritchey, who as you know has written extensively on SQL Server databases recently told me that he jokes with people who have gone to your classes that ‘the bleeding from the ears stops in a few days’. You’re said to be the mental equivalent of drinking Red Bull. What do you think of Grant’s description and the latter analogy?

IBG:

Very good! I pay good money to Grant to promote my classes and I’m glad to see that my investment pays off. More seriously, I love it when people like Grant attend my classes. He’s very bright, and the bleeding is only because he manages to absorb a lot of information and come up with his own ideas. What I’m trying to say is that the ‘bleeding’ is a good reaction. A bad reaction is that your ears get blocked by earwax because you’re not ready for the material.

RM:

Why did you decide to become a teacher?

IBG:

When I try to explain something about SQL to my wife, there was not much interest. When I try to tell her a joke, she never laughs. When I started teaching just as a small part time thing, I realized that people are truly interested in what I have to say, and even laugh from my jokes. As you can imagine, there’s no way back at this point.

RM:

What’s your proudest moment as a teacher?

IBG:

Recently a student who was in his 50s attended my class. He told me that before the course he thought he saw everything and that in the last few years he lost interest and felt worn out. While attending the class he realized that there’s a lot of depth to SQL that he didn’t know, and his passion for the subject was reignited. I felt both proud and happy for him and thought that there’s no better reward possible for a teacher.

RM:

I don’t mean to dwell on this but what do you consider the most difficult aspect of teaching?

IBG:

To me, teaching is what I love doing, so the difficulty is not in the teaching itself, rather in what I do to enable it. I travel most of the time in order to teach, and the separation from my family is the hardest thing for me.

RM:

And how does a teacher become a published writer? Was it a natural progression for you?

IBG:

Even if you know a lot of stuff, your thoughts are not always organized, and you don’t always bother to understand all angles and small details of the subject matter. What’s common to both teaching and writing is that it forces you to organize your thoughts and to make sure that you do cover the small details. Also, writing and teaching complete each other. I think it’s a natural thing for a teacher to write and for an author to teach.

RM:

Is your writing confined to technical subjects, or do you harbour a passion to become a novelist?

IBG:

My writing is confined to technical subjects and I don’t have a hidden wish to become a novelist. Though, I wouldn’t mind if one of my books sold like Harry Potter. 😉

RM:

Did anyone ever help with your work as extensively as you have helped others? I mean by criticism and editing work?

IBG:

I learned and keep learning all the time from others. And actually it’s not necessarily from the more famous people that I learn the most, rather in some cases from those who focus more on the technology without having much public exposure. It would probably be unfair of me to mention specific examples because I did and do learn all the time from many people. But maybe I’ll give just a couple.

Take Steve Kass as an example; he’s extremely bright, and his grasp of logic and SQL is truly astounding. He edited both the 2005 and 2008 editions of the T-SQL Querying and T-SQL Programming books and also participated in writing a couple of chapters for the 2008 edition of T-SQL Querying.

There are no real words I can use to express the learning experience while working on his edits. His newsgroup posts and solutions to SQL challenges are always brilliant and insightful. As another example take Erland Sommarskog.

I bet you that he found a time turner or another way to stretch time, because otherwise I can’t explain how he discovers everything that he discovers about SQL Server. I’m so thankful that he exists because I get to learn new things from him all the time. I also love spending time with good friends over beer talking about SQL, the relational model, solving problems together. It’s amazing how much you can learn this way. I also get to learn a lot from students. Sometimes it’s a solution with an approach that I haven’t thought of. Sometimes it’s a question that I don’t know the answer of, and then it makes me do the research to find the solution.

RM:

Do you observe all the time, like a journalist? Donald Knuth told me that when writing he’s pretty much a hermit and is unable to do anything else until he’s finished. He will not travel to conferences or accept speaking engagements, or undertake any new responsibilities of any kind.

IBG:

I do observe all the time and constantly work on querying problems. Usually I don’t do the research for the purposes of writing, rather write about stuff I already know. The writing part is then a matter of polishing the solutions and organizing those and my thoughts into words. I don’t change my work schedule. I keep teaching and speaking in conferences, and write before or after work. Sometimes when jetlagged due to travelling I get more writing done; if I can’t fall asleep, I might as well write. Unlike teaching, I find writing emotionally draining. But I do it to complement the teaching part.

RM:

Although it’s a language few people read today, have you tried writing in Yiddish? Do you think there would be much point to it?

IBG:

My knowledge, experience and interest level in Yiddish is an empty set, I’m afraid.

RM:

Could you tell me something about the way you work? Do you live a hurried life and work every day, 7 days a week?

IBG:

Most of the time I’m on the road; probably two thirds of the year. During the days I’m either teaching a public class or with a private client. I speak in a few conferences every year. Before the working day I try to go over e-mails. After the working day I try to address e-mails, meet writing deadlines, work on new SQL problems, and learn new subjects. I also love Guinness, so when abroad I usually try to find a pub that serves Guinness and has wireless connectivity. That’s the ideal combo as far as I’m concerned.

When at home I dedicate my time to learning new things and meeting deadlines for writing projects and though in the past I used to work most weekends; lately, unless I must, I do my best to avoid it and to just spend time with my wife and my dogs.

RM:

You’re well known as a mentor and as you know many developers when they start out have other developers they as models. Did you?

IBG:

I don’t know about being a model and I’m not sure I’m even comfortable thinking of myself as one. I simply enjoy writing queries and am glad to share the experience with others that also do. As for people that I see as models, yes there are, there are some models to follow and equally there some models not to. But I think that this is too personal for me to be specific about in a public interview.

RM:

You’re extremely modest. What made you begin? Was it finding a niche and filling it like most entrepreneurs?

IBG:

The idea for the company started in a pub-which is always a good sign! I was not there because it was during a conference that I did not attend. The other five founders sat there over beer and figured that we’d be able to do much more as a group than as individuals. I got a message from my friend Fernando, who’s the company’s CEO, telling me about the idea and asking whether I’d like to be part of the founding group. It seemed to make perfect sense so I did. It was right after the bubble explosion and many companies couldn’t afford keeping their top technical people. But when in trouble, companies were willing to pay top dollars for short-term help from experts that travelled to the customer. I guess we fell in that niche because we were never willing to compromise on quality. So quite oddly, during those times when we started, even though companies struggled with budgets, we still got a lot of work. We started with six people eight years ago; today we have over 100 people spread globally throughout the world. We still never compromised on quality.

From a personal perspective, to me the two most important things about the company is working with people that I trust and respect and being able to do what I like, which is teaching. I feel among friends and family, and I feel fulfilled professionally. I hope to retire in this company.

RM:

What is a great piece of code for you?

IBG:

An elegant set-based solution to a querying problem that for a long time I haven’t found a good performing solution for. A piece of code that solves a complex problem in both a simple and an efficient manner. A piece of code that when you look at you say “I can’t believe that I didn’t think about it myself-it’s so simple!”

This reminds me that once I attended a session about T-SQL tips and tricks, and the speaker showed a complex query and praised the author for the ability to write such code. I think that the ability to write a query that an average developer can’t understand is not something to be proud of. What does prove talent and skill and is something to be proud of is the ability to come up with a simple query solution to a complex problem, such that the average developer can understand.

RM:

You were introduced to SQL during your national service. What was the advantage do you think of learning about SQL in the military?

IBG:

I got introduced to it as part of my computer training in the army. To me the advantage of learning computers was simply that it gave me a profession. I served in the Israeli army, and for us the service is mandatory. I consider myself lucky that I got a profession out of it. Not all do.

RM:

Do you feel part of a SQL community along with others such as Joe Celko and Kalen Delaney?

IBG:

The SQL community is a very lively passionate community with lots of bright people. I do feel part of it and in fact many of my friends I met through SQL. I don’t feel that I contribute as much as others, like Erland Sommarskog, Aaron Bertrand, Alejandro Mesa, and many others, but I do what I can, and I do feel part of the family.

RM:

Finally I’d like to ask about speaking at conferences. Do you feel comfortable as a speaker and what’s the key to delivering a good speech?

IBG:

Funny that you should ask; contrary to teaching classes, which I love, speaking at conferences is a completely different experience. There’s a lot of stress involved. You’re speaking to a large audience; you have a very short period of time with a lot of content to deliver; and you need to keep the audience entertained, not just to teach them practical things. A classroom is different; the audience is smaller and you have more time. You develop much more of a personal relationship with the students.

On the other hand, delivering a session in a conference in front of a large audience has its moments. It’s a different atmosphere. It is a pretty gratifying experience to hear hundreds of people laughing together, or clapping their hands, or gasping.

The way I see it, I’m a teacher. I like teaching. I don’t like as much the other activities like consulting, speaking at big conferences, and writing. But I do the other activities to enable the teaching part. For one, the other activities are sometimes the ones that give you the exposure. Also, the other activities enrich the teaching part.

As for the key to delivering a good speech; I think it is to be really passionate about the topic. Once someone who attended a session that I delivered told me “I didn’t understand anything you said, it was way over my head, but you were so exited about the topic that I thoroughly enjoyed the session.” I should hope that a good speech is a combination of really teaching people new things as well as getting them excited about the subject.