When we hire new employees (at my day job) we start with a phone interview.
I've got a list of a couple dozen questions we run through that give us an idea
of whether we should consider bringing the person in for a more in depth
interview. While a discussion of those questions is probably worth an article in
itself, we'll stick closer to SQL today and talk about just one of the questions
I ask of candidates for senior developer positions.
Here it is:
"A junior developer comes to you with the following problem. They have a SQL
stored procedure comprised of about 200 lines of business logic. The procedure
executes without error but does not return the expected results. Without looking
at the code, what guidance would you offer that would help them solve the
problem?"
Think on it for a minute, I imagine you'll think of a couple different
options and maybe a couple questions. What do you think is the common answer?
Answer: Add print statements to the code.
Other answers I've been given:
- You shouldn't have that much code in a single stored procedure
- I'd have to look at the code
- Have them check the data
In 10% of the interviews they'll suggest using a debugger, either the one in
Query Analyzer or the one in Visual Studio.Net.
So let's talk about what I think about the answers briefly. Adding print
statements isn't the worst idea, similar to adding debug.print or
debug.writeline statements or whatever your language of choice supports. What I
look for is do they go the extra mile, suggest putting one inside every branch,
or putting one halfway to cut in half the lines of code to inspect. Too much
code in one proc? Hard to tell based just on lines of code, but I don't see 200
as excessive - still, I'm open to a discussion on that point. Looking at the
code? Sure you want to look, but I'm looking for someone who doesn't HAVE to
look, they can explain how they solve the problem to someone else in repeatable
fashion. Checking the data? Definitely part of the process, bad data happens far
too often, always a factor to be considered.
Possibly you can tell I'm a fan of the debugger approach. Why? Suppose we
reframed the question to look something like this:
"A junior developer comes to you with the following problem. They have a VB6/VB.Net
method comprised of about 200 lines of business logic. The method executes
without error but does not return the expected results. Without looking at the
code, what guidance would you offer that would help them solve the problem?"
I'd be willing to bet that almost every developer will say that you should
step through the code. Why does the answer change? Developers think of VB6/VB.Net/C#/whatever
as code, but stored procedures as magical things on the server. If you're
using VB6 or whatever you have a very rich development environment - step
through, watch variables, etc, but for some reason developers don't think of
stored procedure code as code. Even when they make that leap, few stop to
realize that they are debugging stored procedures with techniques worthy of the
early nineties.
As far as the interview itself, this question let's me see if they can see
outside their world a little, see how the react to a small challenge, how would
they do as a mentor? Standard stuff I think. While you you might not agree with
the question, I hope you'll think about the range of answers and why so few
developers use a debugger on stored procedures.
