Jeff Moden’s contribution to the SQL community has been outstanding. His views are straightforward, the discussions he provokes are lively, and not only does he know a frightening amount about both SQL and T-SQL, but he’s also refreshing to listen to. The winner of this year’s Exceptional DBA Award is a well-known mentor with a passion to learn, and has all the native curiosity found in the best database developers.

Jeff currently works in Michigan (USA) as the Lead Application DBA and Data Architect for New York-based DiscoverReady, a leading provider of business information and professional services to the legal profession.

RM:

What was your career path to becoming a DBA?

JM:

As with many of my peers, I’m an accidental DBA and the world of SQL Server was thrust upon me by events I had no control over. I had written compiled code to receive, process, bill, and invoice several million call-detail rows of data each month. It was good, fast code but my boss, Al Barash, knew we were going to grow even more rapidly than we had been and was worried about scalability for the future. He had hired a consultant by the name of Rich Bay for a different task. During the various meetings they had on that task, conversation eventually drifted to what I was doing and Rich told Al about SQL Server. To make a much longer story shorter, Rich set up a Windows server, a pair of domain controllers, and the SQL Server. Then, he and I went to the bookstore and he showed me a couple of books to buy to study from. He marked the chapters he felt I should study and then left me mostly to self-study. He was ready with an answer for the occasional questions I had for about a week but I was pretty much on my own. As a bit of a side bar (and I didn’t realize it until years later), Rich didn’t mark the chapters on Cursors and While Loops in the books we bought for me to read. After four days of some pretty intense studying, I started developing real code in T-SQL. I ran into a problem and asked Rich how to assign a value to a variable for each row. His answer was simple and direct. “To do that, you would need a cursor and if you ever use one, I’m all done teaching you.” I didn’t realize it then, but that’s when the proverbial “Anti-RBAR Alliance” was born. Each time I solve a performance problem in SQL, I thank Rich under my breath. I also thank Al, my old boss, for giving me the wonderful opportunity to become an “Accidental DBA”.

RM:

Why do you think it’s important the industry recognizes the work DBAs do? And what has given you most satisfaction?

JM:

To answer the first question, I don’t believe that most people, including some CIOs, actually know what a good DBA or SQL Developer is, does, or can do. I believe that a lot of people, including many C-Level managers, think that databases are “just a place to store data” and that, other than changing backup tapes, a DBA is nothing more than a high-priced baby sitter for a system designed to take care of itself. They just don’t understand the value of their data until the person assigned to occasionally take care of the databases or servers posts a question on an SQL forum that starts with “My database is corrupt and my backups won’t restore” or “We’ve just been hacked…” or “The server is slow…”. Recognition of what DBAs do, like Red Gate has done with the Exceptional DBA award and like Microsoft has done with their MVP awards, helps spread the word that data and the knowledge to take care of it is important and that it takes a special type of person to protect it at all times, even at three in the morning as so many DBAs have done so many times in their careers. To answer the second question as to what has given me the most satisfaction, that would be seeing the product of what I’ve tried to teach all along, which is “Pass it forward”. I get no greater joy than seeing someone’s light come on (and, sometimes it’s mine) and then for them to pass it forward by teaching someone else.

RM:

How much does trying to be an exceptional DBA matter to the job you do?

JM:

It means nothing and it means everything. I say it means nothing because I don’t even think about it as being an “exceptional” effort. I know how important data is to a company and I know how important it is for everyone that touches the data to be knowledgeable about how to manipulate it and protect it. I always try to do it “right the first time, all the time” and I try to help anyone working with data do the same thing. I say it means everything because it’s my job and one of my favorite hobbies. Some people get enjoyment from solving crossword puzzles or playing Sudoku or what have you. I enjoy solving problems in T-SQL. I also enjoy teaching… a lot. The folks at work call me “Professor” because I’ll help anyone and everyone with an SQL problem. If I don’t know the answer, then we discover it together. I love teaching T-SQL so much that I paid my own way to PASS 2010 and a couple of SQLSaturdays just for the opportunity to teach something different to my peers.

RM:

Your answers to queries on SQLServerCentral.com are popular among the SQL Server cognoscenti. Why did you decide to share your knowledge in this way and what do you get out of it?

JM:

It happened a long time ago. I had a problem with deleting duplicates from a table of several million rows and I couldn’t quite figure it out. At the same time, my boss decided to send me to a weeklong course in the implementation of SQL Server. Near the end of that course, I asked the instructor about my problem. He gave me the same technically correct, yet incredibly useless, answer that I had found on an SQL forum: “If you’ve designed your database correctly, you shouldn’t have dupes.” Heh, it was a staging table of third party data and what I was trying to do was to do “everything right.” Disappointed, yet undeterred, I spent the weekend trying to solve the problem. I almost gave in to using a WHILE loop when I remembered something from one of the books I studied called a “self join”. I’d never used one before but thought it might be just what I needed and, darned if it wasn’t! Then I remembered that a couple of folks on the T-SQL forum of Belution.com (long gone, now) had a similar question where we all received the same incredibly useless answer as my instructor had given. I knew what I had just gone through and decided to give those folks the proverbial “leg up” by posting my hard earned solution. While I was there, I noticed some unanswered questions that I’d not seen in any of the books I’d read and thought the answers to the questions could be useful in the future. So, I sat down, did some research and a whole lot of experimentation, came up with solutions that worked, and was able to apply some of them in my job. I posted my answers as thanks for the questions that gave me the opportunity to teach myself something new that I hadn’t found in any books. The response was fantastic. Not only were people thankful but others joined in with solutions of their own and, needless to say, not only was my constant craving to teach satisfied, but I learned more than I had taught. It wasn’t only some T-SQL code that I learned that day. I learned that there are people just like me out there. Over the years, they’ve formed incredible communities of knowledge and the camaraderie is second to none. I’m honored they’ve let me become a part of it. My original intent to sharing solutions that I’d come up with was a little self-indulgence as a teacher and to help some folks avoid the kind of problems that I’d run up against alone. I got much more than I could have ever expected in return. Like I said earlier, there is no greater satisfaction for me than teaching someone something and then watching them as they turn around and teach it to someone else.

RM:

What are the biggest lessons you feel you’ve learnt from the SQL community in the years that you’ve been answering questions?

JM:

The biggest lesson of them all is that people are the most important resources there are. Without a community either at work or on the forums, we’re all just a bunch of individuals struggling to get the job done no matter what the job is. Get two or three people with a common goal together and you’ll find that the cliché ‘the whole is greater than the sum of its parts’, is absolutely true.

RM:

What are the most common mistakes and assumptions which crop up again and again?

JM:

I believe my answer will shock some of the people that know me well. Most of them are probably expecting me to say that RBAR is the most common mistake and it’s actually not. The most common mistake I’ve seen people make is testing with too little data and then assuming that it will somehow be scalable in the future. I believe that one of the most important SQL skills to build upon is learning how to create and populate a million row test table with random but constrained data to meet certain requirements at the drop of a hat. The second biggest problem is actually related to the first. Lots of folks will find code (or other advice) on the internet or in books by sometimes well-known authors and use it on blind faith. Those that actually spend the time to do a little testing rarely take the small amount of time it takes to test for scalability by creating a million row test table. That’s why most of my posts that contain code as an answer, also contain the code to create a pertinent million-row test table. As Sergiy, an old friend from SSC would say “A developer must not guess. A developer must know” and I want people that use my code to have the opportunity to know about my code before they use it. There’s another reason why I post test data development code. It gives other people the easy opportunity to test their own solutions and some of them come up with better solutions than I do! That has happened on multiple articles I’ve written of which the “Tally Ho!” article (about a new type of splitter) is probably the most famous. Without such an article, suggested solution to a problem, and the test code provided, the Tally Table splitter might still have a performance problem. I came up with a solution and someone took my solution and tweaked it to make yet another 20% performance gain in the solution. You’ve just got to love this community. That brings us back to answer of one of your previous questions. People are the most important resource in this. Enable them with a bit a voluminous test data and an idea, then stand back and watch the innovation happen. Heh… and based on your previous question about “trying to be an exceptional DBA”, that’s one of those things I never really consciously thought about, either. We’re all in this together and I try to enable other people to succeed, sometimes even at my own expense. What do I get in return? The sheer pleasure of watching someone’s light come on.

RM:

With SQL Server, there comes a time when you think “This would be a really useful feature, if only they had bothered to finish it.” What are the features you think could be improved upon?

JM:

They actually fixed one of my pet peeves in SQL Server 2012. They’ve finally made it so that you can do a “running total” and some other “previous row” calculations using aggregates like SUM(). Previously, one had to resort to some fairly serious SQL prestidigitation such as the “Quirky Update”. Some other things on my “wish list” are… Rewrite PIVOT and UNPIVOT to be at least as good as PIVOT found in MS Access. Someone spent a lot of time writing the HierarchyID data-type and the code to support it. Couldn’t they have taken it to the next step and have built something similar for “Nested Sets?” Rewrite Bulk Insert to work better and create a Bulk Export. Rework PATINDEX so that it has the same 3rd operand as CHARINDEX so that you can tell it which character to start searching on. Build in a one, two, and three dimensional “splitter” function especially one that will handle the MAX datatypes without the use of XML. Build in a “data generator” function to replace the Tally CTE which many of us use and also make it capable of generating date/time ranges. Create a decent “Dir” function to be executed from T-SQL along with some file handling. Bring back sp_MakeWebTask and direct its output to a MAX datatype. Fix the RAND function so it varies when used on more than one row in a SELECT. There are, of course, more things I’d like to see in T-SQL, but those things are on the top of my list.

RM:

So, which aspects of working with SQL get you really excited?

JM:

Teaching it to someone who wants to learn it. You can’t imagine the thrill I had just a couple of weeks ago when a developer came to me with code that took more than 3 days to execute. In just an hour of teaching her some ideas and techniques, she was able to rewrite the code so that it ran successfully in only 30 minutes! I’ll never forget the look of accomplishment and pride on her face. It was a truly amazing experience.

RM:

Is there any feature with SQL Server that you would like to take a hammer to?

JM:

More than you can imagine. I’ll just leave it at that for now.

RM:

Do you think people recognize the role of a development DBA?

JM:

Yes and No. Yes… because my last four jobs have revolved around that particular role. Some folks are finally beginning to realize that SQL Server isn’t just a place to store data. That intelligent design and planning for scalability are important. That not everyone has the skills to do either even if they have had the proverbial “8 years’ experience with SQL Server”. I say ‘no’ because, as I previously stated and more often than not, C-Level managers just don’t understand what such a hybrid (combination of system, design, and T-SQL knowledge) can actually do for the company, the data that drives it, or the people that drive the data.

RM:

Do you think the kind of people who can be successful DBAs has changed, and is becoming a DBA more of a career choice than an accident?

JM:

On the first question, no. I don’t think the kind of people who can be successful DBAs has changed at all. I think all good DBAs have to be absolute guardians of the data, diplomats, dedicated professional students, master designers, incredible trouble-shooters, teachers, interested in doing the job very well, and have the patience of Job. On the second question and with the understanding that I’m speaking only about the U.S.A., again, no. The reason why is for the very reason I stated before. Not enough C-Level managers understand the value of such a person in such a role. Most IT job descriptions still give a laundry list of required skills followed with ‘and a least 1 year of experience with SQL.’ They still don’t understand the need for System or Development DBAs and so they don’t have such a person. There are comparatively few such jobs on the market. When the inevitable “feathers hit the fan” at such a company, some person with the right stuff stands up to help and becomes an accidental DBA.

RM:

How would you make money from your skills if you weren’t in the job that you are in now?

JM:

I had no idea that there were actually other jobs.

RM:

Who would you say has taught you most about SQL? Your Zen master, if you will?

JM:

I have no one. No personal “Zen master” for SQL. Rich Bay probably comes the closest because he enabled me to learn about SQL Server and T-SQL, but he actually taught me very little about both. No, it’s more like a force made up of the worldwide community of DBAs, developers, and users. If you participate, you learn. I do, however, have a kind of “Zen Master of Enablement” Thank you Mr. Steve Jones for sticking with the SQL Server community all of these years. More than anyone, you have realized that people are the most important resource and you continue to invest in them. Without you, your editorials, and your gentle yet firm guiding hand on SQLServerCentral.com, it wouldn’t be the exceptional forum that it currently is and there would be fewer chances for many of us to truly excel in our professions. You are the consummate Exceptional DBA.

RM:

Do you have anything else to add?

JM: