Knowing good SQL 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.
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…
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. (Okay, uh, maybe not.)
36 Comments. Leave new
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!
Oh hey, here is one more good call. Let’s also have them carry nice laminated cards with the picture of a big IBM computer in the back, and a red star in the middle. Just for identification purposes. Hey , u can use SAML just for kicks .
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!
You are a good man Zach because after the arrogant display of the DBA side I will expect a more caustic reaction from you.
I have Been an architect and a consultant for a while and I have worn different hats in the IT industry and I want to believe that this give me some understanding of both side.
A piece of advise whatever you do, do it for the benefit of the business and not to feed your stupid ego and this is valid for both side administrators or developers or you will be end out without a pay check very soon.
I really enjoy the clash of egos! A reminder to the DBA Santa is also watching you for your next interview.
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!
Funny how DBA ‘s still live in the the 20th century. The time for intelligent systems has come to replace the DBA 🙂
A Developer – yep, I couldn’t agree more. We’re just waiting on developers to write those systems. Got a status update for me?
http://download.netiq.com/CMS/DATASHEET/NetIQ_PB_Aegis_web.pdf
🙂
http://www.oracle.com/technetwork/database/asmwp-130989.pdf
It’s getting close… “Democracy = Freedom from the shackles of the autocratic rule of “The DBA”, aka = “The butcher of of business logic” 🙂
Having worked with those solutions, I can assure you that the developers (ahem) need to put a little more work into ’em before they’re baked. I’m sure you’ll get right on that, right?
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.
Well from my perspective,our ‘DBA’ is actually a Team of sql developers along with a Network person who also manages our Cluster. I work in a hospital with a 6 gig SQL Server database. I never blog, I have a a brand new baby… who has time to blog? The interview questions were *ok* but I think they were a little weird and you seem uptight. Most of your questions seem irrelevant to every day experiences.
Hi, Marge. I’m sorry you felt that way. Best of luck with the little one!
Brent or any other folks,
Does anyone know how Big sites, I mean Big, like
Facebook, handle such high transactions volumes. Are they just throwing in more mem and hardware at the problem as MySpace appears to do?
A Developer – it depends. Facebook needed something fundamentally different than all of the RDBMS technologies out there, so they invented Cassandra. MySpace handled load growth by scaling out with Service Broker, a feature built into SQL Server. You can learn a lot about how existing sites have done it at:
http://highscalability.com/
Thanks Brent,
I guess for these apps, concurrency and isolation levels can’t be too much of a worry? or is it?
Rob
Rob – yep, concurrency and isolation levels are indeed a big deal for these sites. You can learn more about their needs at HighScalability.com.
Any developer who cannot wait for DBA’s to ‘go away’ (um, YYYYYYYEEEEEAAAAAAHHHHHH Mr. TPS boy, like THAT’S going to happen hahahahaha) need to put down the crack pipe. SERIOUSLY??? If you are referring to a strictly DBA role, then that desire does not even make any sense! Are YOU going to admin the servers??? Love the uptime at THAT datacenter, Skippy. If you are referring to the mixed DBA/DB Developer role that is now so commonly referred to as ‘DBA’, then I say: Are that THAT incapable of communicating requirements??? Do you really want THAT much more work??? “Here’s what I am needing to pass to you and here is what I am needing back.” Can life get any easier than that???? Where I’m at now, I’m doing everything from req gathering to db design & dev to UI. Ah, to have one-uh-dem-dare DBA developeeers ta do mah heavy werk.
Brent, good read and loads of fun 😉
When is the DBA right? “Always, kid. Always.”
hahaha, very good!
I disagree with this question if you are a high end developer. What you would call senior (7+ years).
In the last 15 years I have met some really silly DBA’s in my time. Companies that do not pay for talent and get folks fresh out of college and keep them for 20 years.
One lady I knew that was a DBA asked why I wanted a role that could access and modify any object in a development environment.
My answer was simple:
“Do you login with 7 different ID’s in all 7 environments or do you use just one?”
I am leading a team of 7 developers….do the math.
So “typically” a DBA is right but don’t rely on the title or status internally to formulate your own opinion.
could you please write for me about difference between primary key and clustered index?
Thanks in advance.
Hi, Smriti. For information about that, check out a T-SQL development book. I’ve got several of them listed here:
https://www.brentozar.com/archive/2008/08/recommended-books-for-sql-server-dbas/
HA HA! Great article Brent.
But I’m still left with the bad taste in my mouth that all you guys are bogged down with “Stepford Wives Syndrome”. You are looking for an individual that matches only your own idea of a perfect world. Perhaps you guys need some real world diversity training.
Myself, I look for people who I think have some worth they can contribute to my business. Irrespective of whether or not they can answer some smart ass questions in a particular tone and with a particular level of arrogance or confidence.
I like to judge based on whether they are able to do the development job that is needed to be done, and not always insist that the manner in which they do it complies with my small minded view of how people should behave.
My fear is that for every bit of judgementalism that you have shown in here there is an equal and opposite view that may be just as valid – and the person you are grilling is not necessarily going to be of the type to tell you so….
Just my 2 cents worth….
Keep up the good work anyway 🙂
Regards, Dave.
Dave – interesting feedback. Thanks for stopping by!
Hello, the links, above, to posts by Jeff Atwood no longer work. They should now point to the following, respectively:
Who Needs Stored Procedures, Anyway?
http://blog.codinghorror.com/who-needs-stored-procedures-anyways/
Stored Procedures vs Ad-Hoc SQL
http://blog.codinghorror.com/stored-procedures-vs-ad-hoc-sql/
My Database is a Web Service
http://blog.codinghorror.com/my-database-is-a-web-service/
Thank you
Kevin – thanks for the heads up! I changed the links. Have a good one!
Great questions !
Recommend “TOP 30 SQL Interview Coding Tasks” by Matthew Urban, well written, most common SQL coding interview questions in one book.
Hi Grace, thanks for the recommendation, it’s true, this book helps a lot, nice list of most common questions. I like it (https://www.net-boss.org/shop/top-30-sql-interview-coding-tasks-by-matthew-urban).
Thanks Troy, I’m happy I could help. I hope the interview went well.