Interview Questions for a SQL Server DBA

The Interview Process

When I’m interviewing a SQL Server DBA, I have three questions that I always ask.

My favourite interview question is this:

“What is the difference between a clustered and a non-clustered index?”

My second favourite question is this:

“What is the right disaster recovery strategy for my company?”

The first question is one of basic understanding. Depending on their work experience, the candidate may have an inkling about why such a distinction matters, and this lets me know how much I have to teach. What it does not do is disqualify them. I am not averse to teaching, which is why I write on this website in the first place.

The second question should be answered with “it depends”, followed by the candidate asking me questions about my company so that they can figure out a way to begin answering it. If the candidate blurts out an answer without asking questions, well, their résumé goes in the garbage. This is the disqualification question.

I’m mostly self-taught, with formal training to fill in the gaps much later in my career, so I know the difference between “I know this thing, but not the textbook definition”, and “I read this somewhere on Google and have never used it”.

If the candidate uses words that are blatantly wrong but have the right idea (“the blob is sorted on the disk, but the non-blob one is a copy of the blob, but smaller, because you can only have one blob, and it’s shaped like a Christmas tree”), then that’s a pass for me. SQL Server doesn’t care how you describe an index to an interviewer, as long as the syntax is correct, and that’s what Books Online is for.

Heck, I might even say “if you can’t explain it with words, draw it for me on the board”. I love pictures. I’ve drawn many lopsided databases in my time. That’s what the dry-erase board is for: working through a problem that exists in the real world. It’s not there to make someone regurgitate a textbook definition. Again, that’s what Books Online is for.

Here’s a practical example from my school days, as a student and as a teacher: the notorious open-book exam.

In this scenario, the exam taker is allowed to refer freely to their textbook throughout the exam, and the invigilator is there to make sure no one is distracting their fellow students.

The open book exam relies on the exam takers having a working knowledge of their subject. In the case of English literature, for example, it helps to have read the book. Summarised study guides only get you so far — if I’m asking why the love between Catherine and Heathcliff can be described as “demonic”, providing examples from the text, you would have to know where to look.

Even so, the open book exam is unfair for those people who haven’t read the book but have seen the movie ten times and can quote their favourite lines from it. Or if they prefer the audio book, because they struggle to read words on a page, especially under stressful situations. Or their parents read the story to them many times in their youth. Or English isn’t their first language. Or their second language.

If you have haven’t read the book, you will run out of time to answer the questions, because you’ll be frantically reading the textbook for the first time, trying to understand why anyone wants to know what a linked list is for (a very typical interview question for software developers). Meanwhile, you’ve used linked lists before in that job you had writing code for your cousin’s bicycle store, but you didn’t realise that was their actual name.

If I’m asking you, with only a few years of experience administering an Access database for your uncle’s grocery store, to create an index in SQL Server, without using the graphical interface of Management Studio, the first place you’re going to look is Books Online.

As my friend Gail Shaw (b | t) once said many years ago, when you are working on a SQL Server database, you should have two windows open, and one of them is Books Online. Why guess arcane syntax when you can simply look it up?

Which brings me to my third favourite question:

“What was the last mistake you made, and how did you recover from it?”

I’ll go first.

This morning I was working on a stored procedure for a new system. The code called for an upsert-style stored procedure (INSERT and UPDATE in one block of code, to check for the existence of a row of data in order to update it, or insert it if it doesn’t exist). My UPDATE statement was missing a WHERE clause.

Fortunately, the tools that I have at my disposal managed to catch this before the code was ever run. My point is, even with decades of experience, and being able to recite all of the keywords in an UPDATE statement, even the most battle-tested person forgets a WHERE clause once in a while and updates or deletes the entire table.

This is why I love my second favourite interview question, because I get to ask the candidate this bonus question:

“Does the disaster recovery strategy you came up with in question 2 cater for the scenario you just described?”

After all, backups matter only if you can restore them promptly and accurately.

Final Thought

This discussion has made a resurgence thanks to my friend Janie Clayton, who has posited (in my interpretation anyway) that technical interviews are designed to exclude certain people from applying for a position, for arbitrary reasons (university degree, culture, gender, language, etc.), for the sake of some puritanical ideal.

My take is obvious: If you have working knowledge that you can demonstrate, and some experience, you can learn the rest on the job. Every system is different, with different goals guiding a decision. It takes time to become accustomed to a new system, and even the smartest person in the room will have to ask questions.

The question is not “what have you memorised?”, but “what can you learn?”.

By all means, if you’re applying for a specialised role (for instance, performance tuning), then you need to have specialised domain knowledge. For everything else*, there’s Books Online.

* MasterCard did not pay for this post.

Author: randolph

Randolph West is a Microsoft Data Platform MVP, and has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen, or doing voices for independent video games. Randolph is available for talks on SQL Server, and technology in general. He also offers training for junior DBAs. Connect with Randolph on Google+ or Twitter.