An interview with Tom Moreau, SQL Server MVP and Author

Dr. Tom Moreau is an independent consultant specializing in Microsoft SQL Server database administration, design and implementation. Tom is based in the Toronto area, is a SQL Server MVP and writes the “Dr. Tom’s Workshop” column for the SQL Server Professional newsletter.

I periodically get recommendations for Database Geek of the Week, and the nominations fall into a few categories.

• People who I have not heard of. Upon googling them, I often discover that I should have known about them, and that they are folks I should talk to.
• People nominated by the PR firm that works for their company. Generally these are not great candidates.
• People whose names, upon reading, cause me to slap myself on the head and cry, “Doh! Why didn’t I think of him (or her)!”

Tom falls into that third category. He co-authored Advanced Transact-SQL for SQL Server 2000 with Itzik Ben-Gan, and it’s a book to which I refer regularly. I just pulled my copy of Itzik and Tom’s book off the shelf, and discovered that the last time I referenced it was to find how to create temporary stored procedures. Advanced Transact-SQL for SQL Server 2000 is a treasure trove for detailed information on the sort of topics that most other books skip.

Tom happens to be the second person in a row who I have interviewed for Database Geek of the Week who has an advanced degree. The following questions were asked by me and answered by Tom via email.

– Douglas Reilly
###

Doug: I notice you have a doctorate. Could I ask what your degree is in?

Tom: Officially, it’s Experimental Space Science, but before you make any cracks about me being a “rocket scientist”, I should tell you that my PhD supervisor wouldn’t let me base my thesis on rockets, since they have a high probability of failure (although I did analyze some rocket telemetry data through my course work). Funnily enough, the fellow grad student, Steve MacLean, who showed me how to use the word processor (something more challenging than physics back then) went on to became a Canadian astronaut! Anyway, my specific field was molecular physics. I looked at the UV emission spectrum of the ClO free radical, which took part in the degradation of stratospheric ozone. Coincidentally, my cousin also has a PhD in spectroscopy, though he remains in the profession, studying polyatomics.

Doug: What impact has your academic background, as opposed to your practical experiences, had on your database work?

Tom: Well, the fact is that I was doing database work even back in my undergrad years. As an undergrad, I was interested in many things – mostly math, physics and chemistry. I used my programming skills to solve problems in those subjects. I even put myself through undergrad by programming in FORTRAN part-time for a professor.

In grad school, I had a temporary assignment as director of the undergrad labs, where I had to manage the grades of 800 students. My predecessor had handled this using a rudimentary Commodore PET program. I re-wrote the program so that it used a very basic DBMS, based on flat files. Not bad for a 32K machine.

A former graduate of my alma mater (York University) had a small research company that was based on campus. He took me on, the only condition being that I finish my PhD. I soon transferred from the research side of the firm to the software side. I developed C programs that manipulated a passive microwave data collection system. My database was crude but it did what we wanted. If only I knew then what I know now!

My next job was at a railroad. At one point, I worked in their artificial intelligence department doing oil analysis. Ironically, they used spectroscopy to gather the data. I had gone full circle. It was there that I was formally introduced to databases. I found IMS difficult, since it was hierarchical and I thought relationally, even though I didn’t know at the time what “relational” meant. When they sent me on a DB2 course, I knew I had found my niche.

Something that my academic background did give me was problem solving and analytical skills. It doesn’t matter whether you’re trying to solve a physics problem, a database issue, or a Sudoku puzzle, the thought process is similar.

Doug: You are a Microsoft Most Valuable Professional (MVP). What do you think of the MVP program?

Tom: I like it very much. It has put me in touch with many fine people – both Microsofties, as well as other MVP’s. Microsoft values our input and we have had a say in shaping SQL Server.

They don’t award just anyone, so being asked is very flattering. The way you get noticed is by participating heavily in the community – mostly in the form of newsgroup posts. I try to answer posts daily, but sometimes that’s not possible. It’s Microsoft’s way of telling me that I have no life :-). But seriously folks, I really do love answering the posts, particularly those from people just starting out. They need the help the most.

Doug: As far as I can tell, you have thus far resisted blogging. Any reason in particular?

Tom: If things go to plan, I won’t ever have a blog ;-). Most of what I have to say, I say in my column or in the newsgroups. There are only 24 hours in a day. Between answering e-mails, fielding newsgroup questions, and doing my regular consulting, I just don’t find that there is much room left for blogging.

Maybe it’s just me…

Doug: Do you regularly read any blogs?

Tom: In a word, no. Okay, I read Karen Watterson’s blog at the SQL Pro website, but that’s about it. If I don’t have time to write a blog, I don’t have time to read them, either. Where do people find the time, anyway?

Maybe it’s just me…

Doug: I am in the process of writing an article on the role of technical books in the overall career development of a software developer. Do you think there is still a place for books in this world of ubiquitous Internet access, blogs and so on?

Tom: What I will say in favor of using the Internet is that it is interactive. You can’t ask a book a question. You can’t do a keyword search on a dead-tree book like you can with Google, MSDN, etc.

Having said that, I can’t take a blog on the subway to work, but I can take a book. No one is likely to steal a book from me on the subway, though they may want to pinch my laptop! I don’t need Internet connectivity to read a book. I don’t need any electronic appliance to read a book. It doesn’t need a battery to give me what I need.

Call me old fashioned, but I actually prefer books. Maybe it’s just me…

Doug: Your co-author on Advanced Transact SQL for SQL Server 2000 lived in a different country from you. How did you find working on a book with a remote co-author?

Tom: It was fantastic! I was amazed at how we hit it off, right from the get go. We had absolutely no disputes throughout the entire work. Itzik inspired my writing. I just kept going because he kept going. It was only when we were in the final edits that we were able to meet in person – at a conference in Scottsdale, AZ, where we were both speaking. We brought our wives along, so they could meet the guys who kept their hubbies from them for the previous seven months .

(BTW, the original page estimate was 200. The main text ended up being 814 pages- and then there was the “end matter”, such as foreword, preface, appendixes, etc. It turned out to be a big undertaking.)

Doug: Is there any chance of you updating book for SQL Server 2005?

Tom: No! However, Itzik is co-authoring a couple of books for MS Press. I’m looking forward to reading them. I enjoy reading his articles in SQL Mag. They’re always thought-provoking.

Doug: Have you read any good Database related books lately? Any good general software development books?

I tech edited Bob Beauchemin & Dan Sullivan’s book, A Developer’s Guide to SQL Server 2005, due out in May. It’s good for those of us who mostly do T-SQL and want to find out what’s changed in that realm, but also need to get up to speed on all of the new stuff for SQL Server 2005 – CLR, XML, ADO, Service Broker, Notifications Services and so on.

Rational Press puts out a good line of books. They’re topic-specific and about 200 pages long. I did a quick review of Roger Wolter’s RTM-updated book on Service Broker. I learned a lot and would recommend it to anyone. Hopefully, that will hit the shelves very soon (the beta version has been out for many months).

I just read Michael Brundage’s book on XQuery. Very interesting. I understand the language a bit more, now. Too bad SQL Server 2005 doesn’t currently support a lot of the standard, but I imagine that will come.

Next on my “to read” list is Peter Blackburn and Bill Vaughn’s book on Reporting Services. I need to get up to speed on this technology. It’s very cool.

Doug: SQL Server 2005 has been out for a while now. What do you think are the neatest changes to Transact SQL?

Tom: What I can say about the new features would fill a book the size of War and Peace! But here are a few highlights:

• I am addicted to CTE’s – Common Table Expressions (Itzik is, too, BTW).
• Structured error handling is a good addition too and will make life simpler make life easier for the ADO coder because it enables you to handle errors that would have been thrown back to the client in the pre-2005 days.
• I’m still getting used to the PIVOT and UNPIVOT functionality. I was able to find a performance boost in a pivot query over the conventional approach (that’s not always guaranteed, BTW).
• Ranking functions really simplify code – and speed it up, too. I see the death of a lot of correlated subqueries.
• I have been thrilled by my initial investigations of Service Broker (check out the podcast I did with Greg Low from SQL Down Under). Having asynchronous capability within the RDBMS engine will change the way we work on the server side.
• Snapshot isolation is also gonna make a huge difference to scalability, while at the same time giving you logically consistent results. We couldn’t do that with NOLOCK and READPAST.
• Partitioned tables plus online index builds are pure DBA candy. Database snapshots will make my life whole.

I strongly suggest that SQL Server shops do a review of the code they now have with the goal of taking advantage of these cool features. The speed boost you’ll get may head off the expense of an unnecessary server hardware upgrade.

Doug: What do you think about using VB.NET or C# for stored procedures, user defined functions and triggers? What sort of guidance can you give to people looking to properly leverage the ability to use procedural code in SQL Server 2005?

Tom: Initially, it scared me to death! I really didn’t get into CLR until I saw the preview of SQL Server 2005 back in Feb 2003. Once I saw what was coming, I had to scramble to get a basic understanding of .NET. That was a steep learning curve, but at least I started early. Pity those who waited.

My big fear is that developers will abuse the CLR capability in the same way that they sometimes use cursors inappropriately. For the most part, I see CLR functions being used in SELECT lists – as long as they don’t do any data retrieval (although there will inevitably be a few exceptions to this). My concern as a DBA is that I won’t be able to examine the code to see what it does – and I won’t be able to fix it when it breaks. If the stuff is created in-house, I have a chance to influence it. If it comes from a third party, well…

Doug: The changes in the included client tools for SQL Server 2005 are very extensive. On occasion, I find myself remoting into a PC that has the SQL Server 2000 tools installed so that I can do what I need to do quickly without investigating how to do it in the SQL Server 2005 tools. Have you found anything that is terribly more difficult with the SQL Server 2005 tools? Anything terribly easier?

Tom: You’re not the only one that prefers Query Analyzer to SQL Server Management Studio. I have heard much grumbling about this topic. I don’t like how long SSMS takes to fire up. However, I much prefer it over Enterprise Manager. Hopefully, they’ll address the shortcomings, preferably in a service pack, though they usually defer such things to formal releases.

Doug: Many years ago, I independently “discovered” markup languages. My markup language involved lots of @ signs and was not terribly device independent, hard coding lots of things that should not have been. Nevertheless, I was very proud of myself until I came across a much cooler, much better thought out markup language, namely Tim Berners-Lee’s HTML.

Have you ever “invented” anything, only to discover, to your horror, much better prior art?

Tom: I did develop a hypertext app in OS/2 that basically had one .DLL file for what would be the equivalent of an HTML page now. No one talked about the Web or browsers or HTML back in those days. I’m not sure what came first – my solution or HTML. Likely, HTML was just hitting the streets at the same time. When I changed jobs, I was shown Mosaic. The job after that is when I actually got introduced to the Web.

Doug: Can you think of any particularly cool tip or trick, especially in SQL Server 2005, that database developers many not know about?

Tom: That one’s tough, since there is just so much new stuff. Let’s take a look at ranking functions, for example. I think a lot of folks are under the impression that the ORDER BY clause you use inside a ranking function has to match the ORDER BY of your SELECT statement. Not true! Here’s why you wouldn’t necessarily do that. Let’s say that you have a table of students and their marks. You want to list them alphabetically, but you want their ranking by the final grades. Here’s how that would look:

There’s also no reason why you can’t use RANK multiple times within the same SELECT list; just give each one a different ORDER BY clause.

###
Do you know someone who deserves to be a Database Geek of the Week? Or perhaps that someone is you? Send me an email at editor@simple-talk.com and include “Database Geek of the Week suggestion” in the subject line.