how to answer interview question "what is the hardest sproc you have created?"

Yes... like you just did. Absolute honestly is absolutely the best policy.

it's a question to make you actually talk about details and solving difficult processes, so they can get some insight into how you solve problems.

it's not a real, qualitative question that says what was the hardest in your life.

I tend to ask something like "give me examples of the last couple of stored procedures you wrote, and what did they do".

then i'll ask specific details about what they are describing...how many table sdid it join, did it use cross apply or cursors or functions....trying to get the candidate to describe and explain, more than what it did.

It's kind of a weird question. I mean I've built complex queries, but I don't remember that many of them now. Plus, most of the ones I wrote, I probably wrote wrong. I'd say the really hard part isn't dealing with a complex query. It's making what seems like a complex problem into a simple query. Anyone can build some Rube Goldberg style query combining temp tables and cursors and ad hoc t-sql. The real talent is in recognizing that for what it is and thinking through the process to arrive at a simplified set of statements.

I have to agree with Grant that it is a weird question, although not the weirdest I've ever heard in an interview. That was:

"Do you recall what time of day you were born?"

Yes, someone once actually asked me that. Ignoring the insanity of that question, my answer to your question would have been more along the lines of having written some SPs that were more complex than others, and simply ignoring the word "hard" mainly because hard is kind of a negative and when you admit to something being hard it makes it sounds like you might have struggled.

Furthermore, Grant continues to be correct in identifying that the interviewer was trying to get at how you solve complex problems. So pick one that was complex (doesn't have to even be the most complex one - the interviewer will never know) and explain to him how you broke it down into its component parts/subproblems, etc. And then include something about your testing/verification process, because if it was indeed complex the interviewer will want to know how you did your best to ensure accuracy of the result.

I don't think it was a weird question at all and Lowell is [font="Arial Black"]absolutely [/font]spot on about the question. There's so much that you can tell about an interviewee when they answer this question. To explain further...

First of all, if they come up with nothing, then they might have never been used for anything other than a code monkey. That wouldn't necessarily be the fault of the candidate and would require some follow up questions because it could indicate someone who was happy with simply doing a 9-5 job and didn't want to put any more effort into things than that or it might indicate someone that won't get easily bored with repetitive support (and might even like it). But you do have to ask a follow up question or two.

If they come up with something, then it's a talking point and how they talk about it can tell you a lot. It they pull a flat line description of what they did, it could indicate similar to the above or it could indicate that, despite the complexity of a problem, they aren't easily rattled. It might also be that they're just making stuff up and haven't actually done anything beyond the code monkey thing. It depends on some of the more subtle intonations of how they respond. It will also indicate how they communicate difficult or complex problems. If they're enthusiastic about what their solution was, it could show a real go getter and are actually excited about what they do but it could also show an arrogant sot. If the come up with, as Grant said, "Rube Goldberg style query", then that will also tell you about their skill level (or lack of it), etc. Like I said, it depends on how they answer.

Of course, if the candidate did their homework on the company and they know how to be interviewed and what to expect, they'll be well prepared for this question (and it's a common question given in many forms) and already have picked a problem or code task they solved that applies to what the company is doing or the area(s) that the company does.

During an interview in the early days of my SQL Career, I was asked the similar question of "What's the largest number of tables you've joined"? At the time, it was only 6 and I could have just said "6" but I took the opportunity to explain that I'd seen queries with much more than that and that they had performance problems because of accidental many-to-many joins that someone attempted to resolve by the use of DISTINCT/GROUP BY and that I'd use the execution plan in my testing during development to determine the correct logical points to do "Divide'n'Conquer" either using derived tables in the FROM clause (2005 was still on the drawing boards at the time) or Temp Tables to greatly reduce the internal row counts generated and how it took a 30 minute server crippling query that caused TempDB to grow by 50GB down to 3 seconds with no TempDB growth and... and... and...

Of course, the question could be a rote question that some idiot interviewer heard is a good question to ask and doesn't actually know how to follow up and engage the interviewee in good follow up questions. In that case, if the interviewee is smart, (s)he will pick up on that and use it as part of their evaluation as to whether or not they actually want to work for that company.

Expect and embrace these types of questions as an opportunity to strut your stuff. Go into the interview and treat the people like you know them all personally and that you've been working with them for a thousand years without being presumptuous because they're not only looking for someone with technical savvy but they're also looking for a good fit.

Now, based on what I just said, one of my first questions on an SQL Server technical interview for DBAs and Developers alike is "How do you get the current date and time using T-SQL"? How would YOU answer that question?

As a bit of a side bar, I'm mortified as to how many people actually come up with nothing (and I mean absolutely n-o-t-h-i-n-g) on that simple question. And, yes... I have been known to stop an interview right then and there when that happens.

stop the interview on the spot? you are tougher than me, wow.

I know the last time i interviewed, i didn't do well as well as i would have liked.

I got the question " you have an application, and it just stops working , what would you do"

I was interviewing for a development position, and couldn't get into the "guess the situation i'm thinking of"; they didn''t say whether it was stand alone, web based, whether it was talking to a database, no diagnosis or error code, so i felt that question sucked. I started out with general issues, networking, poorly coded loops and try/catch, never even got to database possibilities before we moved to the next question.

Because of that personal experience, i at least try to pick at them from a few different ways to see if they are faking it, vs a person who doesn't interview well but might have the skills.

Hardest proc? Its the report proc where 10 different managers want their department's performance statistics to outperform all other departments. And the final version needs approved by all ten managers.

The report determined which departments got yearly bonuses. Needless to say, you'll never see a proc with more special cases and exclusions and iterations and approval processes than this one I worked on at AT&T.

Hard does not equal Difficult.

I hope the answer is as simple as SELECT GETDATE() right?

Thanks all for your input really appreciate it. I think I have good idea of what people are getting at and makes sense.

You got it completely wrong, you're thinking on dialect and we don't speak dialects, we use a general language called SQL which mandates uses CURRENT_TIMESTAMP according to ISO.

OK, I'm just impersonating someone here to show you that is as simple as that but as complex as having 6 different options to get to the result. 😀

Thank you all I appreciate the feedback. I go a lot of good ideas.

Luis - THAT was funny 🙂