Knowing good questions to ask during an interview with a developer can help you filter out the best candidates from the ones who aren’t the most qualified. There’s a huge difference between “It worked on my machine” and “It scales well in production.” These interview questions will help you filter out the bad apples before you hire them.
10. Explain why DBAs don’t like cursors.
I like to phrase this interview question this way because I’m not saying the DBA is right – I’m just asking the developer to explain the DBA’s point of view. I don’t have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.
The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that’s purely a bonus. (I wish I could say that these concepts are requirements, but in today’s economic market, companies don’t always want to pay top dollar to get the best candidates.)
9. Where do you like business logic – in the app or in the database? Why?
Personally, I like stored procedures because they’re easier for us DBAs to test, tune and tweak. On the other hand, the developer community isn’t always as fond of stored procs. For their side, see these posts by Jeff Atwood:
I don’t mind what arguments the coder candidate uses, but I want to see ‘em put some thought into it. No matter which angle they take, I’ll play the devil’s advocate and prod them with arguments just to see how they react.
8. Explain when and how transactions should be used.
Not In The Oprah Book Club, Oddly
Start with just that open-ended interview question, and if they have trouble getting started, give them a scenario.
“Say we’ve got a table for Orders, and a table for OrderDetails. Someone places an order for two books – Bacon: A Love Story and the hit bestseller Eat What You Want and Die Like A Man. Tell me what happens.”
After they’ve answered, ask them when transactions should not be used. I don’t want my developers wrapping anything inside a transaction unless it absolutely needs to be. (Unlike bacon, which should be used as often as possible for wrapping purposes.)
7. Explain referential integrity and where it can be enforced.
If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What’s an orphan? How do we make sure that we don’t end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?
6. What’s the fastest way to get a thousand records into the database?
I’m not looking for the best answers – I’m just looking to hear that they’ve done some work to performance tune their queries. If they’re doing fully logged individual record inserts, one at a time, into a data warehouse-size system, we’re going to have problems down the road. (Yes, I’ve actually worked with a BI developer that did millions of individual inserts per night in full recovery mode and thought the performance was the database’s fault.)
Bonus points if they link back to the previous interview question and talk about whether or not they should disable constraints or referential integrity during data loads. (I don’t care what their final answer is, but I just want them to know the pros and cons.)
5. What’s the difference between a primary key and a clustered index?
This is almost a bonus question. Most of the time, the candidate doesn’t know because it’s a function of the data modeler or architect, not the developer. However, I want to see how the candidate reacts to tough questions. Ideally, they say in a relaxed tone of voice, “I’m not sure, but I know who I’d ask.” If they don’t mention where they’d go, ask them where they go for SQL Server answers. Speaking of which…
Bonus Points for This Candidate
4. What’s your StackOverflow name?
I don’t need to see a high reputation, but I do want to see an awareness of the site. This interview question serves two purposes: it finds out if they’re serious enough to be active in the community, and it shows them that you’re okay with their community activity. Start a conversation with them about the level of internet time that you find acceptable in the office, and encourage them to share their knowledge with their peers. This sells the candidate on your shop.
3. Tell me about a time when a DBA got mad at you.
This is a spin on the classic interview question, “Tell me about a time when you failed.” Implemented a user-defined function, trigger, CLR in the database, or something else that made the DBA freak out? I want to hear that the candidate listened to what the DBA had to say, good or bad.
If they say it’s never happened, rest assured it’s going to happen soon.
2. How can you tell if a query will scale for production?
I want to hear that they do things like load tests or maybe look at execution plans.
I’m sometimes comfortable when a senior developer says things like, “I can pretty well tell when something isn’t going to scale, because I know the production boxes really well.” The key is asking a followup question about times when things didn’t scale.
1. When is the DBA right?
Always, kid. Always.
If you liked this, here’s a few more of my posts about interview questions for job candidates:
- Top 10 Interview Questions to Ask Senior DBAs
- Top 10 Interview Questions to Ask Junior DBAs (by Tom LaRock)
- Interview Tip: Why The Interviewer Tries to Stress You Out
- Getting Your First DBA Job Without Experience
- Recommended Books for SQL Server DBAs
- How to Get a Job as a SQL Server Expert
- Top 10 Questions to Ask During Your Own Interview
Along with the StackOverflow question, I would ask if they blog. If so, where? Also, what about other forums? StackOverflow isn’t the only community forum in which to participate.
All great questions. I also like to know and get a feel for their local community involvement. So similar to the StackOverflow, I like to see if they attend or even know about local user groups. If they know where the closest SQL Server User group meets and where, bonus!
I have found a very small set of really smart SQL people that don’t post on forums, blog or participate in the community as a whole. It always stuns me but I don’t rule them out because of it. I generally try to bring them into the fold.
Excellent list of questions! Now for those of us out there that are developers, please provide some discussion and resources to these issues from the DBA perspective. Many of us (developers) work in an environment where we either don’t have a DBA involved in the project at any point in the lifecycle, or only have them involved for short-periods of time during the lifecycle. This leaves us without the knowledge or perspective of the DBA during our duties and often creates an environment that, if and when a DBA does come to the project, they are there to fight a fire and don’t have time to explain why something should be or should not be done a certain way or to discuss design alternatives. This leaves us to fend for ourselves using past experiences and what we can gleam from online resources. This of course gives us an incomplete picture and doesn’t address the natural bias towards solutions which are beneficial to the software development process.
Also, while I know that the last question (1. When is the DBA right?) was said tongue in cheek, I do want make a point. While certainly all of the concerns of the DBA are valid and database administration is a difficult and complex task requiring a highly developed set of skills, I’ve found that in most of the projects *I* have worked on, the size and complexity of the “database” portion of the project lifecycle pales in comparison to the size and complexity of the “software development” portion of the project lifecycle. I understand this may not always be true, but in my experience (as well as many other developers I know) it has been. This lends me to believe that our bias towards solutions which are beneficial to the software development side of the project is valid. As always, you must look at the entire picture, not just individual areas, and managing the complexity on a project is no different.
I mean no offense and hopefully my point came across without any. I throughly enjoy your blog and find it an invaluable resource to provide me with technical insight and a DBA’s perspective. It helps me find that balance for my projects. Now please to be learnin’ us!
It’s gratifying to see that I’d do fairly well… I’d definitely ace #4
(See SO id #3043, currently ranked 6th overall there. — Okay, so maybe that one could show a little too much involvement.)
But seriously, I think I have pretty good answers for most of those.
Great post Brent – the right amount of technical and “general thoughts”-type questions (and timely as I’m involved in the technical side of interviewing a new developer).
Cheers, Thomas
My answer to #1 is….(D)O NOT (B)OTHER TO (A)SK. That is what the acronym “DBA” stands for.
Follow-up question…why does most DBAs have this attitude of Mr. Know It All ? Why do they say they are always right ?
Funny you mention it – I’ve got a series coming about why bad people hate good dbas and why good people hate bad dbas!
I love the fact that you list in your descriptions of the questions “I don’t mind what arguments the coder candidate uses..” etc.
I find, though, that candidates have been so over coached that an interview is just like a trivia test or certification exam — that they must have the “right” answer, as if there really is such a thing. I want to hire a guy or gal that knows how to apply cost, benefit, and risk to the environment at hand. There is a good reason why experts almost always have to start a response with “it depends”. It’s not just the consultant’s universal answer.
Even when I put up a data structure like Order and Order Detail Line and ask a candidate to whip a up query on the white board that pulls a certain set of data back, I don’t care if their syntax is wrong or whether they left out a key concepts. I want them to show me their though process, not that they have BOL memorized and no idea what to do with it.
Overall, though, I find that candidates are mystified by these questions that don’t have a right answer, even after telling them I want most to hear about their thought process, not their test taking skills.
Yep, I agree. If I smell somebody giving a canned answer, I’ll ask them to give me several other possible answers and explain why their answer is better. In my “Why do DBAs hate cursors?” question, if the candidate just responds brainlessly, I’ll ask them why cursors are good, and when DBAs do approve the use of cursors.
Response to #3, forgetting the WITH(NOLOCK) hint.
[...] also read this the other day that may be of some use: SQL Server DBA Brent Ozar __________________ George Twitter | A smile confuses an approaching [...]