I’ve written before about technical interview questions, especially of the tricky Brainteaser Interview Questions, and I wanted to take a moment to recount some recent experiences hiring a couple of DBAs.

We’ve had a technical phone interview for some time, written by a colleague, but I think mostly trawled from an internet search of database technical questions.  This undoubtedly has some value, with questions on technical details on the difference, say, between a Primary Key and a Unique Key, or perhaps what ACID stands for.  The problem I find is that day-to-day, a term like ‘ACID’ is almost never referred to, because it is simply (and in some fundamental way) is just expected to be possible and true of a database such as Sql Server – or we might even say prohibited by developers not using transactions.  We needed more structure for the face-to-face bit, and I was fearful about hiring someone who could not even do the DBA equivalent of the FizzBuzz programming exercise.

So for the last year or more, we’ve standardised our face-to-face interview questions to focus on three areas:

• A practical Normalisation question;
• Simple SQL queries;
• Performance Investigation.

In each case, I have taken a practical example of real-world situation from our code-base as the inspiration… and it has been fascinating how helpful the approach has been to better understand the capabilities of the candidate.  I hope it also introduces the interviewee to practical examples of what the role entails.Let’s look at each question type in a little more detail.  We have a print-out over three A4 pages which leaves plenty of room for the candidates (or us) to scribble on the paper as the answers and issues are discussed:

A Practical Normalisation Question

This question starts with a visualisation of a single table called ‘MailingList’, the list of columns and their type, and shows some sample data, of which one column is a comma-separated list of email addresses.

We ask candidates questions like:

• Could you look at the types of the columns and comment if they seem appropriate (e.g. the key column is a BIGINT, and could be a smaller type, perhaps) and the EmailList column is VARCHAR(2000) and we can have a conversation about appropriate sizes and what VARCHAR(MAX) might mean (and ‘is that reasonable?’) and so on;
• What might this table be used for?  The motivation of this question is to find out if a candidate – who can not reasonably know what the table is for – can make a reasonable guess what it is for?  I personally believe the answer is fairly straight-forward – but you’d be surprised how many people struggle;
• Is the table normalised? And this leads especially onto asking them to modify the design and suggest alternatives.

These questions and conversations are a fascinating insight into the DBAs skills, and offer many opportunitites to examine skills or other comments that crop up. For example, whether or not the candidate mentions that BIGINT might be quite a large type for the probable use and requirement, we can ask them if they think it appropriate, and what other types could be considered (and what size are they, and so on).

Simple SQL Queries

It baffled me to find out that many candidates simply could not write SQL. They’d been Database Administrators for years, apparently, but could not write some fairly simple SELECT statements.  So we show them a two-table data model, with a one-to-many link between the tables, and we show then the columns of those tables, and ask them questions like:

• Write some SQL to answer how many loans each personId has (we generally push them to try and answer this referring only to the loan table);
• Ask them to output all the person details in addition to the counts gained earlier (we want to see them use the answer from the first part as a sub-query, to avoid having to have a huge GROUP BY list);
• Ask them a couple of further questions where they write or describe SQL – inasmuch as the first two answers together often exclude listing people with no recorded loans (it’s fair enough, we do pretty much lead them to a solution that does that) 😛

Performance Investigation

In this question we show them a fairly complex query in three slighlty different versions, and with their associated execution plans.  We ask the candidate to explain:

• The broad intent of the SQL (it’s about 15 lines long), what tables it is querying, and could they guess why certain aspects of the SQL were there?  This is all about their ability to read and infer the purpose of some SQL; though again, their ability to infer the meaning might be dependent on their having understood what business we are in, for example;
• To point out key features of the execution plan;
• To highlight areas where they would want to investigate performance issues first;
• To discuss differences between Index Seeks and Scans, which is ‘better’, and so on;
• To discuss changes in the plan as the filters on the query are changed (essentially this line of questioning is looking to uncover their understanding of the optimiser, statistics, and so on).

Interview Experiences

The results of us having the framework of the questions is really fascinating. It obviously drives the body of the interview from a perspective of things we want to get from the candidate, and helps me as an interviewer.  I still need to know the subject area so it does not remove thought from the consideration… and sometimes someone mentions a solution to a problem that you don’t exactly recall how to use yourself (for me the last example of this was someone suggesting the use of ROW NUMBER in a query.  In that case I acknowledged that I did not recall the actual syntax, and could they try answering using an aggregate function instead?

But the biggest general feedback I have, in the context of hiring DBAs, is that it is amazing how many struggle through these relatively simple SQL questions, are confounded by extracting a one-t0-many relationship out of the normalisation question  (and then name the new tables very badly once they have been hand-held through it) and finally have little idea of how to read the execution plans or understanding of how they should prioritise performance investigations.  And just to prove a point, I can’t even think we’ve ever had a candidate who confidently stated what normal-form our table question was in!  (I don’t care that they don’t know the name at all… I do care if they can actually normalise it sensibly).

Probably one of the best observations I have, though, is that some people who sound brilliant based on their words often turn out to be absolutely useless.  One of these choice phrases, I recall, was along the lines of

“You know, I’m the kind of DBA who believes the security and reliability of the data is of paramount importance.”

My heart literally skipped a beat – a like-minded person! …but unfortunately, the candidate proved essentially unable to answer any of the questions to a reasonable level without incredibly painstaking levels of hand-holding. What a shame – but at least the real-world interview questions and the structure and getting the candidate to talk through actual processes really identified the flaws.

My best recommendation for this approach came when we had not one but two candidates who basically breezed through the questions; I became far more confident about the hiring decision, and indeed one candidate even said he had much preferred the face-to-face interview… the technical questions we covered proved he was probably right in that respect, and the conversations we went on to have proved he was socially a good fit too.