July 9, 2009 at 6:34 am
Hi all
I was recently asked to supply 3 questions for use in an interview.
I should say though that the post-holder was only expected to be able to use SQL Server at a fairly basic level (ie not an expert), but it WAS specified in the job advert and all of them claimed to have SQL in their CV (that's resume for those across the pond). Question 3 was in there to "sort the men from the boys".
The questions were
1. What error would you expect from the following:
SELECT AgeGroup, Count(*)
FROM AgeGroupTbl
assuming of course that AgeGroupTbl is an existing table?
2. What steps would you take to identify why a query is running slowly?
3. What is your understanding of the term COLLATION in a database context?
We interviewed 5 candidates and not one of them could answer any question satisfactorily.
Was I expecting too much?
Has anyone else had this experience?
What questions should I have asked or should they have been worded differently?
July 9, 2009 at 6:37 am
I dont think you were expecting too much, everyone should have least got the first one, and I have seen very similar repsonses when interviewing people. It seems that a lot of people do put things in their CVs in the hope that they will be able to 'wing' it until they learn the job.
July 9, 2009 at 7:11 am
I think :
1. that the first question was definitely fair.
2. the second question is beyond using sql at a fairly basic level, but still a good question (maybe this should have been the #3 question).
3. that the third question would "separate the men from the boys" for an advanced position.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2009 at 8:48 am
Colin Bickford (7/9/2009)
We interviewed 5 candidates and not one of them could answer any question satisfactorily.
I'd have been very surprised if they could. In my opinion (biased, I admit), the skill level that I see in job seekers is appallingly low.
I've had a 'senior DBA' who couldn't tell me two types of backups.
I've had a SQL developer with 6 years experience who didn't know how to do error handling or what a transaction was.
I have never had a candidate (even for high-level positions) who could give two index types and explain the difference between them.
That said, for someone expected to have a basic understanding of SQL, the second question's a little advanced and the third unlikely to be answered at all. The first some should have got at least.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2009 at 11:09 am
I would definitely use the first two questions for an entry level type position.
Based on recent experience, we have encountered 50+ candidates who could not answer questions such as these. Many of them also for senior level positions.
Much like Gail, I have yet to see a candidate who could tell me what two types of indexes are, even with some prodding.
Just keep plugging away at it and weed out some of the candidates.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 9, 2009 at 12:06 pm
Okay, I saw this earlier this morning before I left for work, and I want to give them a shot, then I will look (Q1 and Q3) to see if I was close. For Q2, you'll have to let me know if I was close.
The questions were
1. What error would you expect from the following:
SELECT AgeGroup, Count(*)
FROM AgeGroupTbl
assuming of course that AgeGroupTbl is an existing table?
-- Basically that you have a non aggregated column, the query needs to include a GROUP BY clause: GROUP BY AgeGroup.
2. What steps would you take to identify why a query is running slowly?
First, I'd review the query and tables involved. Are functions being used in JOIN or WHERE clause criteria that may be causing table scans. Are there indexes on the tables that are appropriate to the query. Are the statistics up to date (this may be more apparent in the next step).
Second, review the actual execution plan to see if there are other possible inefficiencies.
Third, I may consider running a trace while the query is running if the above steps didn't reveal anything.
Fourth, I would run the query through DTA to see if there MIGHT (with a block of salt) be other indexes that may be beneficial. Ath this point I would also be looking at the missing index dmv's as well.
3. What is your understanding of the term COLLATION in a database context?
This determines how character data is compared and sorted.
Okay, how did I do?
July 9, 2009 at 2:00 pm
Hi Colin, I'm afraid most of these guy's comments are right. The standards out there are very low and I speak from fresh experience having interviewed 4 candidates over the past 2 days for Senior SQL Server BI/DW/DBA positions. Prior to this I interviewed about 20 candidates over 5 months with similar findings.
Although you are looking for someone with a basic level of SQL knowledge I think you need to quantify what that means, if they are going to be installing SQL Server and need to select a certain type of collation then fair enough, although I wouldn't class this as a basic SQL user.
I agree that the first 2 questions should be easily answered by anyone who has taken the time to prepare. For me those type of questions are fundamental and are effectively easy points. 2 contractors I interviewed with a wealth of experience on their CVs, going for a £350 per day position could not tell me what the 2 types of indexes are!
It does get you down but once in a while you uncover a gem and that makes the let downs more than worthwhile!
July 9, 2009 at 2:40 pm
On the second one, it's pretty dependent on how I learned that the proc is "slow".
If, for example, a web page is loading slowly, and the users are telling me that the "database is too slow today", my handling starts out very differently from one where I found the slowest OLTP proc in the database by analyzing a trace.
My take on the kind of questions to ask is based on what are they going to do that will create the most problems that I'll have to fix.
For a person being hired to develop procs, the basic questions I'll ask will be about things like "with nolock", cursors, and nested IF statements. Those are the ones that they better know or I'm getting in more trouble than I'm solving if I hire that person.
For an admin, it'd probably be about backups, restores, maintenance plans.
For someone responsible for overall performance, it would be about execution plans, reading traces, indexing (covering indexes, "include" columns, that kind of thing).
I want to know before-hand if the person will be blasting "nolock" all over the database, more than if they can identify an error that management studio will alert them to anyway.
I'd rather know that they know something about point-in-time restore, than about collations. (Just saw one the other day where the guy thought he could do a point-in-time from a week-old full backup and the log file. No log backups, just thought the log file was adequate. Lost a week's work.)
I'm much more interested in whether they can read an execution plan, or even know what one is, than if they know "where to start" on a "slow proc". Neither of the devs at the last place I worked had ever even heard of execution plans, though they had been building SQL databases for years.
If you work it that way, you'll get what you're looking for, or you'll get someone you can train into what you're looking for. Random basic questions won't do you as much good as you might like.
It's all about what will cost you the most later down the road.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2009 at 2:45 pm
Lynn Pettis (7/9/2009)
Okay, I saw this earlier this morning before I left for work, and I want to give them a shot, then I will look (Q1 and Q3) to see if I was close. For Q2, you'll have to let me know if I was close.The questions were
1. What error would you expect from the following:
SELECT AgeGroup, Count(*)
FROM AgeGroupTbl
assuming of course that AgeGroupTbl is an existing table?
-- Basically that you have a non aggregated column, the query needs to include a GROUP BY clause: GROUP BY AgeGroup.
or include the column in an aggregrate
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2009 at 5:57 pm
I'm actually kind of appalled by the inability to answer these questions of basic developers. I'm especially surprised by the index type question, which by your context is clustered vs. non-clustered.. I especially like the question about NOLOCK, I would ahve asked a question like "Tell me what if anything NO LOCK does (if anything) and where it might be used?" I really do think people believe they can "wing" it while turning out horrifyingly bad code that I'm just gonna have to fix later..
CEWII
July 9, 2009 at 7:51 pm
I agree with GSquared that the kind of questions to ask have to fit the sort of things you are going to ask the people to do.
I'd be more interested to know if they think in terms of sets of data than if they memorized syntax which they can lookup in BOL. For a starter position that mostly involves writing queries, asking someone to describe the difference between types of joins would be good, especially if they can do so using two or three tables that you describe.
If someone only worked in an environment with one or two collations they probably did not have to consider collation at all except when, and if, they set up a database. It can be a once and done thing and goes out of sight.
July 9, 2009 at 8:10 pm
The other thing that comes to mind is that there is a certain inbuilt bias in technical interviews. I could answer a question technically right, but if it doesn't fit the answer the questioner is looking for, it is wrong..
CEWII
July 10, 2009 at 9:11 am
Elliott W (7/9/2009)
The other thing that comes to mind is that there is a certain inbuilt bias in technical interviews. I could answer a question technically right, but if it doesn't fit the answer the questioner is looking for, it is wrong..CEWII
I've run into versions of that several times.
Took some online tests on MS SQL, and the tests were horribly flawed. Lots of questions that really needed a "None of the above" option, but didn't have one. Lots of answers that I know better ways to do it, but the better way wasn't one of the choices.
Heck, one of the tests had 14 of 39 questions on database dev work, that were about CLR. Per Microsoft's numbers, less than 1% of installations actually use custom CLR, and most of that appears to be in triggers. How does it make sense to have 36% of your test be on a subject you have a less than 1% chance of ever using?
Had a written test one time, and one of the questions was about removing the time part of a datetime value. I used the DateAdd(DateDiff) method, and the person administering the test decided it was wrong, because the only answer he knew was converting to varchar. Didn't bother to test my answer, just decided it was wrong because he'd never seen it before.
That doesn't just apply to DBA interview questions. Took an online IQ test one time, disagreed with two of the questions, suggested they be changed in a specific way, and they made my suggested changes.
No test/question is better than the people who write or administer it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2009 at 9:19 am
CLR in triggers.. I don't think I'm a fan of than.. I have built CLR UDFs, sprocs, and TVFs, but triggers and UDTs scare me some.. But I agree 30%+ seems excessive..
I look back at some of my code and still think i could do better..
Now on that dateadd/convert question, I fully agree yours works, the only thing I'd be questioning is which performs better..
CEWII
July 10, 2009 at 9:22 am
Test both versions (convert and dateadd) on a couple of million rows of data.
My tests show the dateadd one is marginally, but definitely, faster. What do your tests show?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply