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.
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
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.
Brent Ozar PLF is a boutique consulting firm focused on understanding your environment and strategy. We partner with you to objectively identify pain points and develop solutions that align to your business goals. Your experience comes first; we share our knowledge and expertise to help you.
More Posts
Follow Me:
