June 6, 2014 at 8:14 am
julian.fletcher (6/5/2014)
And yet ... we're constantly surprised (and disappointed) how badly most people do.
You said a mouthful, there. If you really want a surprise, add just one more question. Ask "Using T-SQL, how do you get the current date and time"?
Yeah, I know... seems way too simple. I've lost track of how many people, both SQL "developers" and "DBAs" (notice the quotes) can't answer that simple question even when I tell them that I don't ask any trick questions and that they should go for the obvious answers to all.
On question 3, I wouldn't ask Front-End Developers to build a trigger at work because I wouldn't want them to. Although the question isn't difficult, it's just something I wouldn't ask of that position. For someone who's going to be an SQL Developer or DBA, then that question is fine.
One of the most important things there is in SQL is knowing how to "count" or create a sequence. I don't go so far as to ask the "FIZZ BUZZ" question simply because a lot of folks don't actually know what MODULUS is, but I do ask them to write the code (and, yes, they should be able to do it on paper) necessary to create and populate a temp table with sequential integers from 1 to a million. You can learn a HUGE amount from that simple test. If they use a WHILE loop, then they are probably a decent but average developer that can be taught. If the use a recursive CTE, then you know they've studied a bit more than average and just need to be educated in methods of performance. If they do a CREATE TABLE and populate it with a ROW_NUMBER() in a CROSS JOIN query (pseudo-cursor), hire them. If they use SELECT/INTO using the same method but use the Identity FUNCTION to create and populate the table all in one step, hire them at a higher rate. 😀 If they used a Tally Table or Tally CTE (cascading Cross Join of constants), don't let them leave the building until they agree to be hired. :w00t: Make sure that you ask them why they used whatever method they used and use it as an opportunity to talk about performance methods, minimal logging, etc, to make sure it's not something they just learned by rote. If they answer "It just seemed like the right thing to do and can't answer any other questions about how it actually works, then they learned it by rote and if you hire them, you're hiring a liar. And, yeah... there will be those that can't even begin to write such code even though it's so simple.
Of course, I'm just kidding about using just one code question as the reason to hire someone but... it's an incredibly simple question that anyone worth their salt should be able to do and, as I suggest in the paragraph above, is going to be a stong indicator of how the rest of the interview is going to go.
As an FYI, I've been known to stop an interview if they can't answer the date and time question and that's always the first question. Keep in mind that some people will "fumble about" a bit because they can't believe that you're asking such a simple question and might need an assurance that the question really is that simple but, if they don't have a clue, (and I've tested this by continuing some of the interviews after a person gets it wrong), I absolutely guarantee the rest of the inteview will prove that the person is not qualified for the job in any way, shape, or form no matter how much "experience" they supposedly have on their resume.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2014 at 8:56 am
Justin, if it helps, I never use "pop quiz" questions when I interview because I find that they don't give me enough information about a candidate's experience or ability to learn or what their interests are. Instead, I try to ask open-ended questions. You can still weed out people who lie about their experience because they won't be able to answer these questions either.
For example:
"What databases/versions have you worked with?" If the candidate mentions two different things like DB2 and SQL Server, I'll ask "Which one did you like better? What was something you found easy in SQL Server that was more challenging in DB2?" [My answer would be about the fact that the DB2 version was extremely outdated, so I wasn't able to use a Split function in DB2, but I found 3 ways around that.] Or if they mention two versions of SQL Server, I'll ask "What were your favorite improvements between 2005 and 2008?" [My answer would be intellisense, but that since you have to have at least Management Studio 2008 and SQL Server 2008 to take advantage of intellisense, I was unfortunately only ever able to do that for one project.]
I never ask obvious yes/no questions but I get them a lot at interviews. "Do you work well both on a team and independently?" YES! "Can you work off of specs?" YES!
June 6, 2014 at 8:56 am
julian.fletcher (6/5/2014)
I work for a company of about 50 people in Oxford and am involved in the recruitment process - we're looking for developers with at least a couple of years' experience in SQL and C#. Part of the process involves asking the candidate a couple of SQL questions, questions which we think aren't too tricky and which shouldn't prove to be too difficult for the sort of people we're looking for. And yet ... we're constantly surprised (and disappointed) how badly most people do. Although the occasional interviewee does answer without any trouble (and usually ends up with a job offer!), we were wondering if the questions are Just Too Difficult.
Julian, I've been loosely involved in recruiting in your catchment area a number of times in the last five years or so and your experience is fairly typical. You may be able to cut down on your costs by using brief phone interviews, which are generally cheaper to conduct than f2f. Start with this one...
Jeff Moden (6/6/2014)
julian.fletcher (6/5/2014)
And yet ... we're constantly surprised (and disappointed) how badly most people do.You said a mouthful, there. If you really want a surprise, add just one more question. Ask "Using T-SQL, how do you get the current date and time"?
You've mentioned this one before a couple of times, Jeff. Here in Maidenhead, not too far from Oxford, we recently had a small recruitment campaign and we were able to put this little gem to the test. Man, you were bang on. There was a fail rate of about 15%.
Perhaps 75% of the candidates couldn't adequately describe the difference between inner and outer joins - and of course, every last one of them described themselves as a "TSQL Expert".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 6, 2014 at 9:00 am
Thanks very much for all your comments - greatly appreciated that you've taken the time. As far as answers go, this is what we're looking for:
Question #1
1 1 1 21
1 2 1 21
1 1 1 21
1 2 1 21
1 1 1 21
1 2 1 21
1 1 1 21
1 2 1 21
2 1 Null Null
Most people usually get the first three correct but fail on the fourth.
Question #2
"The surnames of all people over 60 years old." Everybody gets this, eventually.
Bonus point (never yet awarded) for noting that they don't give exactly the same answer (as alluded to above).
The second is likely to be better for performance, assuming there's an index on the DateOfBirth column. We usually have to prompt to get this.
Question #3
"Maintain an updated date column on the table." Everybody gets this, eventually.
Bonus point (occasionally awarded) for mentioning there might be a recursion problem.
But we're really looking for the fact it will fail for an update of multiple rows. It's unusual for people to see this without a fair bit of prompting.
Once we've got that far, most people say that the solution would be to join to inserted in the update (or have "WHERE EmployeeID IN (SELECT ...").
I should have said that we're looking for developers to work on all parts of a particular product; both the C# and the SQL. We're not big enough to be able to have separate teams for each.
Asking how to get the current date and time might be a good way to break the ice - thanks. I might use it next time. But I think asking a candidate how they would create and populate a temp table with sequential integers from 1 to a million would just be too difficult given that most can't answer our simple three questions. And yes, this is after we've had an initial telephone interview.
Julian.
June 6, 2014 at 9:03 am
Ask "Using T-SQL, how do you get the current date and time"?
So would you consider this a valid answer? "I think it's CURRENT TIMESTAMP but if I was blanking on it I'd google it to be sure."
I've worked with so many language that sometimes I mix them up, so I often google things even if I know them. And sometimes even when I "know" something, a quick search reminds me of something I forgot. IMO, knowing how to find the information you need is just as valuable as already knowing it. And double checking yourself is a good skill too. If you've never forgotten a simple piece of syntax, then you never had a baby who didn't sleep through the night.
June 6, 2014 at 9:16 am
kimberly_lehman (6/6/2014)
Ask "Using T-SQL, how do you get the current date and time"?
So would you consider this a valid answer? "I think it's CURRENT TIMESTAMP but if I was blanking on it I'd google it to be sure."
I've worked with so many language that sometimes I mix them up, so I often google things even if I know them. And sometimes even when I "know" something, a quick search reminds me of something I forgot. IMO, knowing how to find the information you need is just as valuable as already knowing it. And double checking yourself is a good skill too. If you've never forgotten a simple piece of syntax, then you never had a baby who didn't sleep through the night.
That might keep me from ending the interview but it would be an indication that the person really hasn't worked with T-SQL that much because it's one of those fundamental things like knowing what a SELECT is used for. Based on the response above, I'd ask if they knew what "Books Online" is and how to get to it. I'm amazed at how many people don't know that, either.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2014 at 9:17 am
julian.fletcher (6/6/2014)
.. I should have said that we're looking for developers to work on all parts of a particular product; both the C# and the SQL. We're not big enough to be able to have separate teams for each...
Shame...I'm nice, I work bl00dy hard, I'm available in two weeks and I'm 25 minutes from Oxford 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 6, 2014 at 9:31 am
kimberly_lehman (6/6/2014)
Ask "Using T-SQL, how do you get the current date and time"?
So would you consider this a valid answer? "I think it's CURRENT TIMESTAMP but if I was blanking on it I'd google it to be sure."
I've worked with so many language that sometimes I mix them up, so I often google things even if I know them. And sometimes even when I "know" something, a quick search reminds me of something I forgot. IMO, knowing how to find the information you need is just as valuable as already knowing it. And double checking yourself is a good skill too. If you've never forgotten a simple piece of syntax, then you never had a baby who didn't sleep through the night.
I believe GETDATE() is specific to T-SQL and CURRENT_TIMESTAMP is ANSI SQL function.
I think still learning
***SQL born on date Spring 2013:-)
June 6, 2014 at 9:39 am
You could potentially miss out on some great employees if you let your ego get in the way of making an objective hiring decision. I've been working with T-SQL for over 10 years and I've helped to make a lot of money for the companies I've worked for. I've also had a very wide variety of experience which I think makes me a valuable asset, but it also means that I don't have to write that particular piece of code frequently enough to have it memorized. I don't mind admitting that I don't know everything. I figure out what I don't know.
June 6, 2014 at 9:52 am
kimberly_lehman (6/6/2014)
You could potentially miss out on some great employees if you let your ego get in the way of making an objective hiring decision. I've been working with T-SQL for over 10 years and I've helped to make a lot of money for the companies I've worked for. I've also had a very wide variety of experience which I think makes me a valuable asset, but it also means that I don't have to write that particular piece of code frequently enough to have it memorized. I don't mind admitting that I don't know everything. I figure out what I don't know.
That seems valid. However, if you're trying to get a T-SQL developer job, you should at least prepare to don't confuse most functions. I've interviewed some people who claim to have worked with Oracle who presented for SQL Server and wouldn't get correct the Oracle or the Sql Server option.
Most people won't be able to differentiate inner joins from outer joins and some even consider left join different from outer join.
If you've worked over 10 years with SQL Server, you should know that most answers are on BOL and you don't even need Google. I worked for a company for 5 years using SQL Server and made them make a lot of money but that didn't make me good on T-SQL, it only made me an "Expert Beginner".
June 6, 2014 at 11:41 am
Good thing this wasn't a pop quiz on the difference between "your" and "you're".
June 6, 2014 at 11:54 am
dotnetkim (6/6/2014)
Good thing this wasn't a pop quiz on the difference between "your" and "you're".
LOL :hehe: My fault.
I know the difference, I'm not sure why did I write it wrong this time.
June 6, 2014 at 2:29 pm
Q1 is simple enough and is simple join techniques, I wouldn't expect to see any issues there.
Q2 requires people to know about SARGability. While a definite skill I'd like to see in an applicant, I find many are largely clueless.
Q3 requires them to know the possible implications of recursive triggers and a little more detail to deal with interview nerves. A simple inclusion: We bulk update this table.
I personally don't find these questions overly difficult, but only the first one is a "book" question. The rest are experience questions.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 6, 2014 at 2:45 pm
ChrisM@Work (6/6/2014)
julian.fletcher (6/6/2014)
.. I should have said that we're looking for developers to work on all parts of a particular product; both the C# and the SQL. We're not big enough to be able to have separate teams for each...Shame...I'm nice, I work bl00dy hard, I'm available in two weeks and I'm 25 minutes from Oxford 😉
And bloody damned good at SQL, to boot!
--Jeff Moden
Change is inevitable... Change for the better is not.
June 6, 2014 at 3:02 pm
dotnetkim (6/6/2014)
Good thing this wasn't a pop quiz on the difference between "your" and "you're".
If I'm trying to hire a proof reader, I might care. If I need someone that's good in SQL Server, I wouldn't care. 😉 It's a common typo.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply