“A tree with a job.”
When it comes to hiring a SQL Server developer, we can pepper candidates with HR-type questions (“Tell me about a time when you had a conflict with a co-worker and how you resolved it.”) but that doesn’t give us a good sense of a developer’s T-SQL skill level. Here are five questions that will give you a good idea how experienced and skilled a developer is.
1. “WHEN IS IT OKAY TO USE A CURSOR?”
Cursors are the most widely misused T-SQL tool out there because beginning developers writing them haven’t yet learned to think in sets. Once we learn how cursors really behave, it’s tempting to swear them off for good. Senior developers ought to be able to provide an example of when it’s really okay, such as a script that does index maintenance or DDL commands.
- Junior Developer answer: “Anytime.”
- Developer answer: “Never.”
- Senior Developer answer: “When there’s no way to accomplish the task in a set but you have to go through multiple iterations.”
2. “WHAT ARE THREE CODE CHANGES YOU’D MAKE TO SPEED THIS QUERY UP?”
The trick here is having code that might be passable but still leaves plenty of room for improvement. This isn’t a question a manager can ask; an experienced developer will have to come up with the code and decide if the suggested improvements will work. (If you’re the one writing the sample code, here are a few ideas:
user-defined functions, table variables, unnecessary sub-queries, and non-SARGables in the WHERE clause.)
- Junior Developer answer: “I dunno, looks good.” (reaches for something insignificant to change)
- Developer answer: “Oof! Who wrote this?” (goes on to list examples)
- Senior Developer answer: “Here are a few things I see…”
(goes on to list examples and sprinkles in a story about how they learned that by experience)
Although it’s nice to get contextual questions back (like if any indexes exist that can be leveraged) but really it’s about spotting purely code-based inefficiency.
3. “WRITE CODE TO CHECK IF ANY RECORDS EXIST.”
A very common code scenario involves checking for the existence of any records that match certain criteria (or just for a table as a whole). The trick here is that all we care about is a yes/no answer. We want that check to be quick and cheap.
- Junior Developer answer: “SELECT COUNT(*)…”
- Developer answer: “SELECT COUNT([field name])…” (Bonus points if they say the field is indexed.)
- Senior Developer answer: “SELECT TOP 1 1” or “EXISTS (SELECT [fieldname]…)”
Related: Jes explains the fastest way to get row counts in SQL Server.
4. “WHAT IS PARAMETER SNIFFING?”
When it goes bad, parameter sniffing is a tricky problem to identify for developers. It can make a query perform like Dr. Jekyll one minute and Mr. Hyde the next. There are some things you can do to mitigate risk with parameter sniffing, but just understanding its nature is a big step. Bonus points to anyone who explains how they dealt with parameter sniffing in the past.
- Junior Developer answer: “I’ve never heard of it.”
- Developer answer: “It’s something to do with the query guessing wrong.”
- Senior Developer answer: “It’s when a query uses a plan based on a different parameter value than the one being passed in and the resulting execution is potentially awful.”
5. “DESCRIBE HOW YOU RESEARCH A PROBLEM YOU’VE NEVER ENCOUNTERED BEFORE.”
I’ve yet to meet a good developer who wasn’t also a good researcher. Our success depends on our ability to fill in our knowledge gaps quickly. Books Online is great for syntax but not insight. Technical books are only good if they address your exact problem and you can’t copy/paste directly from them. (Speaking of copy/paste, “search online for the exact error text” is a great answer too.) StackOverflow and Twitter are high-traffic places where people are eager to help you.
- Junior Developer answer: “I go to Books Online or look in a technical book.”
- Developer answer: “I search online, especially on StackOverflow or MSDN forums.”
- Senior Developer answer: “I check sites like StackOverflow or throw the question out on twitter with the #sqlhelp hashtag.”
These are the big questions that came to my mind. What questions have you found helpful in hiring developers? Let us know in the comments!
71 Comments. Leave new
For #3 EXISTS(SELECT * FROM T) is always optimal. The columns, the top count and potentially a DISTINCT do not matter at all with this pattern.
The big reason I like choosing just one column for the EXISTS subquery is that I don’t want to see any SELECT * in my code, and I don’t want to deal with mentally exempting any code from that rule. I’ll get the itch to replace it every time I see it, whether it matters performance-wise or not.
Wouldn’t EXISTS (SELECT NULL … be a more accurate statement the? SQL does not use the record set, only the existence of a record, therefore you are “selecting nothing”
That being said, I always use “SELECT 1” because selecting null just seems “dirty”
Giving into my pedantic tendencies, SELECT TOP 1 ‘exists’ FROM….
I used to use EXISTS(SELECT 1 … but now I always use EXISTS(SELECT *… When you understand how the optimiser decides which columns it needs to select you will realise there is no difference in the time it takes / efficiency to do SELECT *, SELECT 1 or SELECT NULL. It’s also sometime useful when writing the code to be able to highlight the SELECT * FROM … in the editor to execute the line just to see what columns are there.
This is exactly the reason I use SELECT * with EXISTS.
Regarding #1, a possible answer could also be:
when the set based solution doesn’t scale really well beyond millions of rows (because some set based solutions scale exponentially because of the joins). The cursor is slow, but scales linearly.
(luckily most of these issues have gone away with window functions)
Set-based iteration… e.g. large dml … ftw!
Please elaborate on your windowing functions comment. Generally interested as I’ve the feeling I’m missing out on something important.
#5. “DESCRIBE HOW YOU RESEARCH A PROBLEM YOU’VE NEVER ENCOUNTERED BEFORE.”
Junior Developer answer: “I go to Books Online or look in a technical book.”
Developer answer: “I search online, especially on StackOverflow or MSDN forums.”
Senior Developer answer: “I check sites like StackOverflow or throw the question out on twitter with the #sqlhelp hashtag.”
I’d even go as far especially if search yield nothing, post the issue in those sites ( StackOverflow, SqlServerCentral etal) and link it in your #sqlhelp tweet or even ask help/dm/pm friends if they encounter (referring to the link).
Depending on the problem, I don’t see what’s the issue with Books Online.
ps: is throwing the question on Twitter without doing research really senior? :p
Yes, it depends on the problem. Books Online is great for when you forget syntax, MSDN documentation is good for more extensive coverage. But when you have a specific problem — often times an error message or things not behaving as you expect — places like StackOverflow are very likely to talk about that exact problem and how to solve it. If I’m hiring a developer, I want to know they can not only research a problem, but also research it efficiently.
I wouldn’t expect a developer (or anyone else, for that matter) to throw a question out to twitter without having done some looking on their own first. Sorry that wasn’t very clear.
Depends on the problem, if you have the error message I prefer to search directly in the web with the error message.
Sometimes you can have a great source of solutions in your peers.
if you have a problem with performance, go to the forums, post a sample code or describe what you need. Twitter has shown to be of great help with #sqlhelp
To be honest, every time I have an issue, I just throw it into google and see what comes up first. Usually MSDN or StackOverflow, sometimes SQLServerCentral. If I’m lucky, a detailed blog post by someone. Sometimes a blog post of my own 🙂
Tell me one or two stories about indexing.
What do you know about cxpacket?
When would you update statistics?
What bulk insertion method do you know?
Tell me something about Backup & Recovery.
You have only got a Raid 5 and just the C drive. Partitioning, yes or no?
If they can tell me anything at all with a little spark in their eyes, I’ll still have them, depending on their softskills.
If I need to cut them off mid way of their answer, I’ll like them, depending on their softskills again.
If they surprise me with more knowledge than me and they have the softskills … well ;D
But if they have no softskills at all, I wouldn’t want them near me, no matter how much they know.
I don’t care all to much about wrong answers, as long as I’m not looking for a senior.
When I’m looking for a senior, he just has got to be a mega Icon for my juniors.
And when I need a senior right now to fix my server, I’ll try to get Brent Ozar to help ;D
If all you’re going to have on your SQL Server is RAID 5 on C:, the senior developer will get out of there as quickly as possible. 😉
Good observations, Peter!
I’d be tempted to run too ;D And I’ve seen far worse.
But still not every DB has just got to have a RAID 10 😉
Consider a webshop selling freezers to eskimos running a SAGE system. A senior would ask if they’d not be better off using pen and paper. 😀
You are looking for a developer not an administrator.
I think it is better try to ask for tricks to get subtotals in a single query.
Window functions FTW! 🙂
“It Depends”. Rollups, Cubes, and “grouping sets” in the GROUP BY will also produce subtotals/totals/granddtotals and can be more efficient than the “Window Functions”.
Very true. But a senior will always be able to tell me stories about every one of the questions.
As I said, wrong answers don’t matter.
What I would want to see is, that a developer is not just the coding monkey.
A senior will most probably have enough experience or even a MCSA. He’ll know about window functions, and who am I to doubt a MCSA certification? 😉
#5. “DESCRIBE HOW YOU RESEARCH A PROBLEM YOU’VE NEVER ENCOUNTERED BEFORE.”
StackOverflow
Twitter
are great
BARCAMPS or MEETUPS are the best if it can’t be resolved quickly.
For #5 it’s great if you can list off specific people’s work you would look towards. If it’s XML or Extended events, I’d go to Jonathan Kehayias first, then Erin Stellato when I found Jonathan’s work above my level. Kendra Little specializes in indexing and tuning. Paul Randal gets into wait types and database consistency. SSIS you’d want to start with Jason Strate. Doug Lane runs circles around me in Reporting Services. If they can mention that they’d trust the answers on SQLServerCentral written by Gail Shaw and Jeff Moden more than anyone else then they’ve just aced this question.
If you can go even further and mention that you can make Jeff Moden happy with two bottles of Bud, you can stop the interview and start writing up the offer letter.
More than just knowing where to look, I really appreciate people knowing who to trust. BTW, I also appreciate the people I trust putting this work out there, so thanks Doug!!!
Yeah, that’s helpful especially when you’re interviewing with someone who recognizes those names (though I wouldn’t say mine is in the same tier as the rest). And thanks for the compliment, Steve!
I like to use a few different SQL functions as one of the tests for whether this is a junior person or a more experienced person. Things like having them explain how they would make use of NTILE or FOR XML PATH(”). It feels like (for the most part) people who can accurately describe situations to use those snippets in TSQL seem to have more experience in many other levels of SQL as well. Of course you wouldn’t use this exclusively as some people know a lot of syntax but still can’t put all of them together into working code but as a quick and dirty litmus test it has been working for me.
Yes, and if they express some enthusiasm for XQuery, have them quarantined immediately.
hilariously true
One of the questions I always ask is: ‘Tell me the difference between 1-1, 1-M, and M-M relationships and give an example of each. You would be surprised at how many people, even Senior DBA candidates, can’t answer the question sufficiently.
You’re right, I would be surprised…and probably horrified.
I love cursors. And I love cursors inside cursors. I only use them for overnight processing and not for OLTP. I always use the local and fast_forward options. I ensure I have proper indexing on the tables being used. I have never had an issue reported to me that a cursor is causing a problem. My environment is small to mid-sized – no Terabyte sized databases or billion of rows tables. To me, the cursor has become to SQL what the banana has become to the “healthy” fruit world – the banana has become hated for, um, I’m not sure what reason (belly fat?). I’m sure I am showing some level of naiveness on these issues and if this classifies me as a Junior Developer (or lower :), hey, I’ll take it. I love the Brentozar.com site, read the blogs almost every day, use the free sp’s a bunch, and have learned a ton (and yet I’m still naïve – go figure) from this site. This post (blog reply) has been done for infotainment purposes.
If your processes are running well within their allotted windows then there’s no need to fix it unless you have the time and want to improve your skills (what DBA has time?). If it’s creeping to the point that you’re either getting complaints or close to it, buy just about any book written by Itzik Ben-Gan.
Many ways of doing things work on smaller amounts of data. As the data grows, the size of the request grows, or you move on to a different job where it already did all that growing, you need to grow your skills along with it. If the data is growing at a descent rate, there’s a good chance anything with the phrase “cursors inside cursors” will need attention beyond indexing eventually.
Perhaps another interview question to ask is what’s the largest database you have worked with, for just this reason. If you did what works, you did it right. However, things change as you move up in size, and experience with larger databases forces you to be more senior in your skills.
Steve, I agree that a good question would be about the size of the databases you work with and what challenges did you face in working with large databases and what did you implement to deal with those challenges. Shall we move on to the dastardly Scalar-valued functions now? 🙂
The answer for that one is the same as the cursor.
If you can do it set based, do it set based. If it’s a small number of iterations then you can use a scalar function or cursor if needed. If it’s a large number of iterations then find a way to make it set based.
Both will take your set and iterate through it row by row. Not so bad when you have 1,000 rows, not so good when you have 1,000,000,000 rows. The exact breaking point will depend on how long you have and how long each iteration takes.
Most of this goes back to two things. How big is your database (or at least the part you’re working with), and how much do you want to hear people like Itzik Ben-Gan and Jeff Moden tell you to think about what you want to do to the column, not the row.
Heh… it might be why folks that use cursors in such a widespread manner still have to call them “overnight” runs. 😉
I never justify one method over another just because there might only be a handful of rows. The method for solving the problem on 10 rows should be the same as solving it for a million rows and is usually easier and shorter than the many forms that RBAR can take. Except for certain extreme and very rare cases, I almost always test for the proverbial “million row problem” because someday it’ll turn into one.
As someone else pointed out, the paradigm shift to being able to think set-based instead of procedurally is to 1) stop thinking in rows, start thinking in columns and 2) realize that every SELECT is actually a loop or “pseudo-cursor” (term coined by R.Barry White on SQLServerCentral) except that they run at near machine language speeds instead of interpretive speeds.
Tell me how you feel about NOLOCK.
Junior Developer answer: It’s great! I use it all the time!
Developer answer: I never use it.
Senior Developer answer: Sometimes I’m defeated. (Hangs head in shame)
When it comes to late night snacks at work, it’s much better to have NO LOCK on the fridge than to have to pick the lock. 😉
one question I’ve asked in an interview with a candidate, is…
Is SQL Server optimistic or pessimistic out of the box?
the candidate supposedly had years of SQL experience, but could not answer the question.
It just raised a little red flag…
-d
oh, and if they know the answer and also know the “oracle” answer. That’s a big plus…
I guess I just got promoted to senior developer.
I won’t bore you with the interviews I’ve conducted over the last several years but I will tell you that most of the candidates that I’ve interview never made it to the level of questioning portrayed in this fine article.
If you really want to find out something interesting about the candidate right up front, explain to them that you don’t ask trick or esoteric questions and that the first couple of questions are going to be very basic just to get them to relax a bit. Then, no matter how much experience they say they have on the resume and it doesn’t matter if they’re applying as a Front-End Developer, DB Developer, or DBA nor what level position they applying for, ask them this one simple question as the first question.
“How do you get the current date and time using T-SQL”?
I won’t describe why this simple question is always my first question nor what I’m looking for other than the obvious answer(s) but I will tell you that you’re in for a real shocker as to how few (only 7 out of 38 in the interviews I’ve recently conducted, all claiming a minimum of 5 years experience with SQL Server) can come up with *any* correct answer. The really bad part was that only 2 of the 6 DB Developers and 1 out of the 10 DBA candidates (the DBAs all claimed at least 10 years experience) came up with *any* correct answer.
And, yeah… I forgot to mention that the other 22 were Front-End Developers. Only 4 of them came up with *any* correct answer. I’m not so shocked by that as the numbers for the DB Developers and DBAs.
Regarding #4. “WHAT IS PARAMETER SNIFFING?”
The “best” answer, “It’s when a query uses a plan based on a different parameter value than the one being passed in…” does not answer the question actually asked. Parameter sniffing is the process SQL Server uses to create an execution plan, and it uses the only value it has, the one being passed on that call. There is a potential problem with SQL Server’s method, as was explained, but that comes on later executions and is not in itself “parameter sniffing”. In fact, no sniffing is done, which is what causes the problem. I suggest we either rephrase the question, such as “What problem can be caused by SQL Server’s use of parameter sniffing?” or revise the response to answer the question actually asked.
OK, let’s role play. I’m the interviewer, and you’re the interviewee. If you answer the question this way, what impression do I take away?
Regarding 3. “WRITE CODE TO CHECK IF ANY RECORDS EXIST.” The method you have mentioned works fine but we can still improve it. I have tried following options with considerable amount of records. The 4th options is the best if we need to check if data exists in a table.
–Table Creation
CREATE TABLE testCounts
(
ID INT
)
GO
INSERT INTO testCounts VALUES ( 100 )
GO 10000
–Method 1
DECLARE @Counter AS BIGINT = 0
SET @Counter = (SELECT COUNT(*) FROM dbo.testCounts)
If @Counter > 0
BEGIN
PRINT 1
END
–Method 2
If EXISTS (SELECT 1 FROM dbo.testCounts)
BEGIN
PRINT 1
END
–Method 3
If EXISTS (SELECT TOP 1 1 FROM dbo.testCounts)
BEGIN
PRINT 1
END
–Method 4
If EXISTS (SELECT COUNT(1) FROM dbo.testCounts)
BEGIN
PRINT 1
END
—
I’m not sure if Pawan Khowal comment was meant to be a joke? but Method 4
“If EXISTS (SELECT COUNT(1) FROM dbo.testCounts)
BEGIN
PRINT 1
END”
Doesn’t work! It will always Print 1 even if the table has no rows as the query returns a result with a value of zero. Perhaps he should of also tried it with no records on the table – Testing!
It muct have been a joke given the count has to count and exists will short circuit.
@ Jonathan – Thank you very much ! learned new thing. My bad I haven’t tried with empty table.
Question – I am the manager of our data team, but only because I am the administrator of our electronic health record and do all of the system configuration, so I know where on the end user side the data I want/need is entered. I am looking to hire a database developer, but without knowing the technicalities and unsure of some “simple” questions and answers I can have HR use for phone interview, to follow process and to get someone with at least 2 years of SQL experience. I don’t need the senior developer (I’m on a budget), but need someone who knows more than I do. Suggestions on how to proceed? Sorry for being the newby, inexperienced one here – just want to find the right person for the job 🙂
C. Levy – this isn’t really an open Q&A format. You’re best off going to a Q&A site.
I give this test an F!
If you are hiring someone to do a SQL Developer job, there is not one question there, that if they did not know the answer to, that they would not be able to do their job. In other words, if I don’t know the answer to “What is parameter sniffing”, will that stop me from doing my job? NO!
The ranking is also bogus. If I go to Books on line first and find the answer in 2 seconds, I don’t need to go anywhere else. How in the world does that make me a junior developer instead of a senior developer? I think I would call that efficient! Or, so when I do use Books online, am I acting as a jr developer, but when I choose one day to go to stack overflow then on that day I am acting as a mid-level. Give me a break! I’ve been doing SQL Development for 20+ years and sometimes go to books online. Guess I am just reverting to being a jr developer, eh?
The interview should be directly related to the job and if you can’t do that, don’t interview. Get someone that knows how to interview and correlate the questions to the job.
I had a 5 question interview once, then asked the guy, “How many of these items will I need to know to do the job?” His answer, ZERO. WTF?
I have taken college courses in Education and part of that is “How to construct a test”. You would be surprised at how hard it is to make an accurate test of the subject matter. The test then needs to be tested, with validation tests. As in Content validity, Face validity, Curricular validity, Criterion-related validity, Predictive validity, Concurrent validity, Construct validity, Convergent validity, Discriminant validity, Consequential validity.
I have been on way too many interviews where the “techie” thinks they know how to create a valid test. Or think their little set of questions are good. Do you know that some of the so called “professional” online tech tests that getting only 60% of the questions correct was the highest score on a national average. That means the test gets an F, not the developers!
I think in the above questions the only one that is relevant is to a day to day job is, “WHAT ARE THREE CODE CHANGES YOU’D MAKE TO SPEED THIS QUERY UP?”
This is open ended and could be answered multiple ways, which would give you insight into their ability to think through a problem, which is what a developer needs to do!
Also the question about “check to see if any records exist”, the fastest way is to query the system tables, because you only need to return one row to get the answer. Since you only asked how to check, not “what is the best way”, then any of those answers are as good as the other. Without having qualified the question, there is no possible way to rank them, certainly not in the interviewees mind. If they say count(*), that answers the question, as asked! You only asked, “how to check”, not the fastest, the best, the most efficient, the possible ways!
But again, none of the ways mentioned are jr, mid or senior. I use them all depending on the environment and how I feel at the moment. It does not make me jr., mid, or senior, not even close.
Thanks for playing, but no cigar!
Ron – there’s a saying: do you have 10 years of experience, or do you have 1 year of experience, done ten times over?
If you’ve been doing SQL development for 20 years and you don’t know what parameter sniffing is, I humbly submit that the F goes to you, not the test. Start the next year of your learning journey here:
http://www.sommarskog.se/query-plan-mysteries.html
You should learn to read what I said, I never said I don’t know what parameter sniffing is. I only indicated that not knowing it would have never prevented me from doing the job.
You also missed the main point of my response. Which is, knowing techie SQL questions has nothing to do with a valid test.
The point is, knowing SQL and devising a VALID test are two different disciplines. And I can tell from your response, you are still focused on the SQL techie side. Nothing wrong with that, but creating a valid test is a whole other discipline. I once had an Astrophysics professor, that knew this and tested, with validation tests, every one of his test questions before considering them to be valid for the subject matter.
So let me ask you, since you posted this “test”.
Which validation tests did you use to determine this as a “valid” test? Here they are, so just pick the ones you used.
Validation Tests:
Content validity, Face validity, Curricular validity, Criterion-related validity, Predictive validity, Concurrent validity, Construct validity, Convergent validity, Discriminant validity, Consequential validity.
So if you did not use any of these validation tests, then my point is made.
“If I’m hiring a senior developer for their query tuning skills” Yes, but that is not the way your “test” is worded.
“I’m asking relevant questions about whether or not this candidate can solve the problems I’m having today, the new ones I’ll have tomorrow, and how much they’ll grow with the challenges.”
And this can only be determined with the validation tests. You might think your test is valid, but if you don’t test the test, it most likely is not.
There is a big difference in thinking it is a good test and proving it. When inexperienced “testors” create a test, then test the test. It usually fails validation and then they are surprised.
If you don’t have training in how to create a test, then it will most likely be riddled with assumptions, just like your five questions. And even in your reply you changed the wording and have shown some of those assumptions that were hidden in your questions.
That’s the whole point of validation tests. Just to be clear the subject I am addressing is not SQL or SQL development, but rather “How to create a valid test”.
And I think if you tested them, your 5 questions would prove not to be a valid test!
Ron – OK, great, show us your example of a SQL developer test.
I’m not hiring anyone nor did I post one as an example, “that will give you a good idea how experienced and skilled a developer is.”
He did and I am commenting on what was posted.
All I am saying is testing is a whole other ball game than knowing SQL and putting together some “techie” questions.
Creating a valid test and testing the test is a completely different discipline.
That’s just a fact and that is what validation tests PROVE!
Just like we would validate code. What’s the problem with validating a test?
OK, cool. If you don’t like our test, I’d totally recommend creating your own. Thanks for stopping by!
“OK, cool. If you don’t like our test, I’d totally recommend creating your own. Thanks for stopping by!”
Brent I am not interested in creating a test, nor do I need one!
Not sure what the point of your comments are. I made objectively clear statements and your comments do not relate at all to anything I wrote?
Ron – so, uh, if you weren’t interested in a test…why are you here?
It sure feels like you came by to throw stones at something totally unrelated to you. That seems like an odd thing to do knowing that your domain, RonCashConsultant.com, is linked to all of your replies. I don’t know that I would want people Googling for “Ron Cash” and coming to this particular thread.
But to each his own.
It sounds like you’ve set the bar pretty high for how thoroughly vetted questions must be in a tech interview. I’ll wager if you go to 100 interviews, no more than one or two will have questions you will approve of in that respect. Hiring managers don’t have the time or knowledge to write such impeccable questions, nor the budget to get someone else to write them. So, as much as my questions (which I at no point referred to as a “test”) may infuriate you for their lack of rigor, they are questions that nonetheless help me make a better-informed estimate of a candidate’s skill level.
Frankly, the assertion that I can accurately gauge a person’s experience level only through methodically validated questions is utter nonsense. I’d argue more for what each of these five questions tells me, but I see little gain.
“I at no point referred to as a “test””
You did not use the word “test” but of course it is a test. It has questions with expected answers. How would you define a test, would a test NOT have questions and answers?
“Frankly, the assertion that I can accurately gauge a person’s experience level only through methodically validated questions is utter nonsense.”
I never said that, my reply was specifically dealing with your 5 questions, which I can specific examples of why they are based on invalid assumptions.
Here is one more you referenced, someone giving the answer of looking at various websites as being higher than a jr.
Well not sure if you realize this, but some companies actually restrict that and a person is not able to do look up various web sites on the job. I have been in those environments. I have worked at over 100 different companies as clients (so obviously I can pass the “techie” tests), including Dept. of Homeland Security (restricted web access), Bank of America (restricted web access), Wells Fargo (restricted web access), and dozens of others. So if I was used to not being able to look up those things on the web, then I would be looking up answers in books and SQL Sever disk based help. So how would that make me a Jr. level developer? Since that is the only thing that was available.
My point is your 5 questions are filled with assumptions.
And if a test is not tested, then it cannot be proven to be valid. This one sentence is an unassailable fact and is axiomatic in it’s nature.
If you would simply be objective and not include so many assumptions abut what I am saying, then just show me where that sentence is incorrect then I might even learn something.
Okay, let’s back up a minute. This is not a test, not in the sense there are right and wrong answers. If you tell me you use Books Online in an environment where internet access is disallowed, of course I’m not going to peg you as a junior developer if that’s the only resource you have. These are questions with three sample answers. There are *many* more possible answers than that. It’s up to me as the interviewer to ask follow-up questions to get at context. Yes, I’ve worked places where there’s either no internet access or Websense restricts the bejeezus out of it, so I am context-aware.
My questions may have assumptions built in, but they’re not the only five questions I ask, and not the only three answers I accept for each when evaluating a candidate.
Thanks for taking the time to leave such a detailed comment. Here’s why I don’t agree with it:
If you go to Books Online and only take two seconds to examine the answer for correctness, context, and warnings before incorporating what you saw in your code, you’re making huge assumptions that will, at some point, cause problems. (I know because I’ve done it.) Stack Overflow helps people solve problems. Books Online is more like an owner’s manual. It contains very little context — it won’t tell you when a feature is a bad idea (at most, it occasionally hints at degraded performance). Here’s an example: If I wanted to know whether Nested Loops in my execution plan are a problem, would I know by reading this – https://technet.microsoft.com/en-us/library/ms187871(v=sql.105).aspx.? Or would I be more likely to know by reading Stack Overflow questions like this – https://dba.stackexchange.com/questions/40280/how-to-optimize-a-query-thats-running-slow-on-nested-loops-inner-join ? In my mind, there’s no contest. I’m trying to avoid hiring someone who is uncurious and just says “good enough” as soon as they possibly can. A lack of curiosity is what keeps developers stuck repeating the same first year of their career, as Brent mentioned.
I disagree on this point as well: “if I don’t know the answer to “What is parameter sniffing”, will that stop me from doing my job? NO!” If I’m hiring a senior developer for their query tuning skills, I want them to know how to recognize a commonplace, 300-level query tuning issue. If you don’t think it’s commonplace or important, work with a hundred or so clients and see if that changes your mind.
You say a test needs to have “Content validity, Face validity, Curricular validity, Criterion-related validity, Predictive validity, Concurrent validity, Construct validity, Convergent validity, Discriminant validity, Consequential validity.” That’s fabulous for a standardized test, I’m sure. I’m not interested in that. Instead, I’m asking relevant questions about whether or not this candidate can solve the problems I’m having today, the new ones I’ll have tomorrow, and how much they’ll grow with the challenges.
“Here are five questions that will give you a good idea how experienced and skilled a developer is”
In our further discussion you are now filling in many details that are not in the original post, which is good and definitely a better approach. But I look at what I read and respond to that.
However think of this. You have the question about “WRITE CODE TO CHECK IF ANY RECORDS EXIST.”
Then you go to state that certain responses would indicate, in the back of your mind, jr, mid, senior experience level.
Well the one interviewing has no way of knowing that you have this mysterious way of ranking their answers accordingly. This to me would be no different than, “sorry for my choice of words’, having a hidden agenda.
If you phrased the question according to your ranking, that would be a fair question, i.e. What would you say would be a jr, mid, and senior level response to the question. Or what are the various ways of checking.
But if you ask, “WRITE CODE TO CHECK IF ANY RECORDS EXIST.” And I answer COUNT(*), then I answered the question 100% percent correct, no matter whether I am jr, sr, or the Chairman of the Board for the ANSI SQL Institute. Question asked, question answered and I am still a senior Developer, my answer would not indicate anything that would change that.
There is nothing in your question that would make me think that I would need to answer any different.
But then you are evaluating my answers based on some ranking that I know nothing about!
I answered the question 100% percent correct, but unknown to me you have some sort of ranking that now rates my 20 years of experience as “jr”.
How can that be thought of as a valid test?
How is that valid? Remember I am responding to your original post, not all the new “qualifying” details.
I have made a point to ask these in every interview I take!!
I hoping someone will shoot me down here but …
RE: #1
Can a Cursor be useful if I need to update a lot of rows in a table and TempDB is not large enough to hold the transaction. Would it be okay to use a Cursor to break the update into parts?
Jodrell – I get nervous there because I hear a couple of things that aren’t really related. TempDB doesn’t hold transactions, and cursors don’t necessarily mean that the updates aren’t done as a single transaction. Best to hit the books on that one.
Okay, my previous assertion was wrong TempDB doesn’t hold transactions.
TempDb holds [many types of temporary objects](https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017).
Would it be acceptable to use a Cursor to intentionally slow down and break up a very large and complex UPDATE that has a tendency to cause TempDB to spill to disk.
Jodrell – I’m being gentle here, but the answer is no. Batching can make perfect sense, but generally speaking, cursors aren’t usually the answer to batching. Check out this technique: http://michaeljswart.com/2014/09/take-care-when-scripting-batches/
thanks Brent, exactly the kind of constructive rebuff I was looking for, its good to make sure there is a way to do that task.
You bet! Glad I could help.
[Comment deleted by commenter’s request]
Ummm… heh… “hire candidates well before the interview” probably isn’t going to work out well. 😉
Love that Ron. I’ve failed numerous home-spun SQL tests because a) the IT Manager needs an ego boost b) The agent only has one answer written down c) I’m just not on the same page as the “SQL Trivial Persuit” quiz master.
Unless lives are literally on the line (medical / military sectors) I suggest hire likable people, then prune away the ones that don’t step up to the task after a few months.