05 September 2005

23951 views

16 0

Database Geek of the Week: Itzik Ben-Gan

Itzik Ben-Gan is a writer and mentor, he writes a monthly column for SQL Server Magazine and co-authored Advanced Transact-SQL for SQL Server 2000.

Itzik Ben-Gan is a writer and mentor in the development community. He writes a monthly column for SQL Server Magazine and co-authored Advanced Transact-SQL for SQL Server 2000 for APress books. He is one of the founders of Solid Quality Learning, a global provider of education and solutions for the Microsoft database platform, and serves as its principal mentor.

The following questions were answered by Itzik via email.

Doug:

Tell me how you got involved in database-related work. Was it a natural fit or an accident of convenience?

Itzik:

The first time I heard of SQL was in a course I was taking in the army. I fell for it immediately. After the army I was involved in networking, programming, databases and data warehouses, but databases weren’t necessarily the focus.

This experience gave me a good taste of the different aspects of computing. At some point I realized that SQL is by far my favorite area, and I started diving into it vertically. Once you delve deeply into a certain area, where the small details are, it’s more interesting and fulfilling. A similar thing happened with teaching.

Doug:

Looking at your bio on the Solid Quality Learning web site, I see you have a number of Microsoft certifications. How did you become a Microsoft certified database administrator? How did you find the testing, and do you feel that the requirements reflect what a database administrator for Microsoft database products needs to know?

Itzik:

I needed the certifications to become an MCT and maintain the trainer certification. I find that the certifications are a good tool for someone who wants to get into a learning frame and set certain goals. Without goals, it’s hard to maintain self-discipline. The certifications are not an indication of extensive knowledge and experience, but are a starting point. Out of the certifications I did, the DBA one seemed more serious and indicative of true knowledge than the others.

It’s important at the early stage of your career to cover subjects horizontally, to have a taste of different areas, and then delve into the one you feel is best for you. In this respect, aiming at multiple certifications can be a good thing. This horizontal knowledge will be handy once you choose an area of expertise. It’s easier when you’re young to free your time for such things. Later on, you become so busy and have so many commitments that it’s harder.

Doug:

You were the founder of the Israeli SQL Server and OLAP Users Group. What made you found the group in 1999, and what keeps you involved with it?

Itzik:

Nothing fancy, I must admit. I was in the army reserve with Tomer-Ben Moshe, who used to be the person in charge of database technologies at Microsoft Israel. Tomer asked me whether I’d like to found and manage the group, and it seemed like a good idea. I was new to the concept of user groups, but a short time before that I participated in one-in Redmond, I believe. It was such a good experience that I thought it would be nice to have one in Israel.

It’s a good group, and I’m happy I got involved. We run monthly meetings on the first Monday of the month. We give two lectures plus a Q&A session and also give a stage to people looking for work, and to employers looking for employees. It’s amazing to see people who have been taking part in the meetings since day one, six years ago.

Doug:

You are also a Microsoft Most Valuable Professional. What do you think of the program?

Itzik:

It is a wonderful program. I first became part of it about six years ago after I started managing the Israeli SQL User Group and participating in the SQL newsgroups. The program’s focus is the SQL community, and that’s great. I find the newsgroups a great tool for people looking for help, and also for people looking for knowledge and ways to put their knowledge into action.

If I had to name one aspect of my learning experience that was the most prominent, by far it was answering questions in the newsgroups. While consultancy projects give you important experience and expose you to real-world scenarios, newsgroups centralize problems database professionals face on daily basis. Especially with SQL and T-SQL querying problems, which are my main interest, you’re exposed to tons of real-world problems. By trying to solve them, you improve your skills. That’s the perfect dojo [place of enlightenment] for SQL practitioners.

Another great thing about the program is the people who are involved in it. Most of our company’s members are MVPs who met through the program. You gain friends for life. Furthermore, the SQL Server development team is unique and special. We interact with them in private forums and also meet every year in the MVP summits. These meetings are amazing. We just sit together and talk, usually about upcoming and future releases.

Doug:

Without violating any non-disclosure agreements, can you tell us what sort of work you are doing these days?

Itzik:

I’m not sure if this would breach my NDA or not, but when I fly back home tomorrow, the first thing I’ll be working on is enhancing the fence in my garden. Apparently, after two years, my dogs figured out that they are able to climb over the fence where the passion fruit bushes grow. My in-laws are our neighbors, and my mother-in-law is terrified of the dogs. But come to think of it, there are more urgent things on my agenda.

I’ve been busy in the last couple of years teaching my advanced T-SQL class around the world, privately and publicly. I’m looking forward to teaching the new version, which covers both 2000 and 2005. Solid Quality Learning really gave me the opportunity to focus on what I like to do best-teaching-and I’m grateful for that.

There was a period during which the training business was so slow that you could only make a decent living and meet your financial commitments by focusing on consulting. For someone who’s a trainer in his heart and soul, not being able to teach as much as I like and maintain the right work balance is dire.

I’m also doing a lot of writing. I maintain two columns in SQL Server Magazine . I’ve been writing for them for five years now and they’re family to me.

I’m also writing a new book, which should be out shortly after SQL Server 2005 is released. Deadlines are pretty tight, so I’m using every bit of free time I have to work on it. I’m a third of the way through.

Doug:

When you program in .NET, what is your favored language?

Itzik:

T-SQL is my focus, so I’m not doing CLR programming. I try to maintain enough knowledge about it to be able to hold discussions that compare T-SQL and CLR, but I don’t go much further than that. My choice of language would be VB.NET, since I have more experience with VB than C.

Doug:

The client tools for SQL Server 2005, while still in beta, are a huge change from previous client tools. Is there anything that developers will find particularly cool in the new tools?

Itzik:

Oh yeah. I have to say that my favorite things don’t have to do with the fancy stuff, but rather the small things. Writing so much with T-SQL, there are several things in SQL Server Management Studio that I find extremely useful. The fact that you can author in a disconnected mode, for example, and that you can change the connection without opening a new window are fantastic. I also find the execution plans zooming window tool very cool.

Doug:

Have you read any good database-related or general development books lately?

Itzik:

I find the series The Art of Computer Programming by Donald E. Knuth helpful with algorithms and logic in general. These books are not database or SQL specific, but general computer science books. Many of the algorithms that are discussed are applicable to SQL programming as well as understanding some database internal structures, such as balanced trees.

I also like Joe Celko’s books. He writes on standard ANSI SQL solutions. He recently published a book about trees that I would recommend, Joe Celko’s Trees and Hierarchies in SQL for Smarties.

Doug:

When I interviewed Douglas McDowell, he said that you would be the person to whom I should ask the following question: There are a number of changes to T-SQL built into SQL Server 2005. What are you most excited about?

Itzik:

My favorite one, by far, is the ROW_NUMBER() function. This function assigns sequential integers to rows of a resultset, based on a specified order, and optionally within partitions of rows. It sounds like a small thing, but the function has numerous practical applications beyond the obvious ones for ranking and scoring.

My second favorite new feature would have to be recursive Common Table Expressions (CTE). Finally you can write set-based, standard recursive queries with T-SQL.

As an example of how powerful these new features are, here’s a sneak preview from the book I’m working on. The following piece of code efficiently assigns left and right values to each node of a tree, representing nested sets relationships:

100

101

102

103

104

105

106

107

108

---------------------------------------------------------------------

-- CTE Code That Creates Nested Sets Relationships

---------------------------------------------------------------------

DECLARE @root AS INT ;

SET @root=1 ;

-- CTE with two numbers: 1 and 2

WITH TwoNumsCTE

AS (SELECT

1 AS n

UNION ALL

SELECT

) ,

-- CTE with two binary sort paths for each node:

-- One smaller than descendants sort paths

-- One greater than descendants sort paths

SortPathCTE

AS (SELECT

empid, 0 AS lvl, n, CAST(n AS VARBINARY(MAX)) AS sortpath

FROM

dbo.Employees

CROSS JOIN TwoNumsCTE

WHERE

empid=@root

UNION ALL

SELECT

C.empid, P.lvl+1, TN.n,

P.sortpath

+CAST(ROW_NUMBER() OVER (PARTITION BY C.mgrid

-- *** determines order of siblings ***

ORDER BY C.empname, C.empid, TN.n) AS BINARY(4))

FROM

SortPathCTE AS P

JOIN dbo.Employees AS C

ON P.n=1

AND C.mgrid=P.empid

CROSS JOIN TwoNumsCTE AS TN

) ,

-- CTE with Row Numbers Representing sortpath Order

SortCTE

AS (SELECT

empid, lvl, ROW_NUMBER() OVER (ORDER BY sortpath) AS sortval

FROM

SortPathCTE

) ,

-- CTE with Left and Right Values Representing

-- Nested Sets Relationships

NestedSetsCTE

AS (SELECT

empid, lvl, MIN(sortval) AS lft, MAX(sortval) AS rgt

FROM

SortCTE

GROUP BY

empid, lvl

)

SELECT

FROM

NestedSetsCTE

ORDER BY

lft ;

-- Output

empid lvl lft rgt ------- ------- ------- ------- 1 0 1 28 2 1 2 13 5 2 3 8 8 3 4 5 10 3 6 7 4 2 9 10 6 2 11 12 3 1 14 27 7 2 15 26 11 3 16 17 9 3 18 25 14 4 19 20 12 4 21 22 13 4 23 24-- DDL & Sample Data

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.Employees') IS NOT NULL

DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees

(

empid INT NOT NULL PRIMARY KEY,

mgrid INT NULL REFERENCES dbo.Employees,

empname VARCHAR(25) NOT NULL,

salary MONEY NOT NULL

);