We sometimes help clients interview DBAs and Developers for their team. Requirements vary depending by position, but sometimes it’s important to test a candidate’s TSQL skills.
While I can test candidates thoroughly on strategies for TSQL (when they’d use certain constructs, the pros and cons of various approaches), if what you care about is skills writing complex TSQL there’s one surefire test: a lab.
The first step is to pick your dataset and environment, then dream up the TSQL query you’d like your candidate to write. Next, test it with one of your own staff members and make sure the problem is suitable for the job you’re trying to fill.
But you may not want to give candidates access to your production, or even development data — after all, they don’t work for you yet.
Good news, there’s an easy way to do this online! Check out the Stack Exchange Data Explorer. You can assign the candidate a query to write using this tool online.
How to make it work:
- This only works for in-person interviews. Because human nature.
- You’re going to need a moderator and at least 30 minutes, because the candidate will need to figure out the schema.
- A history of queries is kept in the tool. You’ll need to let them know they can’t search that history, and it’s up to you if you’re going to let them use Books Online (hey, we all use it in the real world).
If you don’t want to deal with the hassle of the query history online, you can always download a copy of the Stack Overflow database and set up your problem on a locally installed test instance of SQL Server, too.
The secret test: if your candidate likes writing TSQL, they’re going to love the challenge and it will energize them.
Happy interviewing!
12 Comments. Leave new
Sounds like fun – do they then get to lead the code review session for extra credit?
And, have you ever partnered them with someone to see if they can work cooperatively?
I’d hate to work without my ‘pile of files’, though…
Oh, that’s actually a great idea about code review! I have done code review questions before– those are *terrific* interview questions.
Hi Kendra,
I am interested into downloading stackoverflow database schema only instead of data and schema. I have already download 9 GB of stackoverflow database from utorrnet, Will you please share schema for the same?
I’m not sure that the schema only would work for testing tsql writing skills. You wouldn’t be able to compare performance of the queries?
Yes, You are right. but for some cases like asp.net developer we just need to test their query writing skills only. We can’t expect them to work on performance.
OK. I am going to go out on a limb here and mention: it IS the Developer’s job to work on Performance. Of course, a DBA has many tools to determine where things are pinching shut on a day-to-day basis. But initially BAD queries are one massive reason I am *constantly* working on production performance as a DBA. I have taken to continually teaching Development about performance, what to watch out for and how to use Exec plans to detect issues before they go to Production. It is their responsibility to test and fix this as much as possible before it “gets away from us.” As Markus Winand points out, “The most important information for indexing is how the application queries the data.” (Markus Winand, http://use-the-index-luke.com/sql/preface)
Okay. Then download a different stack exchange db. One that has a lower volume. I must state that I completely reject your assumption that DEV’s shouldn’t be concerned with performance.
This is SO awesome. I have tried to do something similar in the past, or at least have the candidate write some code on the fly rather than ask generic questions. Another Kendra Keeper!
I’ll have to disagree on the “this only works in person” part. The way I get around that is to use a T-SQL test as a tech screen, and then have a conversation with the person about their answers. You’ll find out REALLY quickly whether or not they actually did the work themselves.
Also, regarding these kinds of tests, I tell candidates to PLEASE use Google, BOL, or whatever else they’d actually use in the real job. But I expect them to be honest about their source(s) when we have our chat. I find that there is high correlation between great Google-fu and people who can GSD.
The book from the picture looks so convincing I’m only 90% sure it doesn’t exist…
It’s a real book. Did you try a search for it?
http://www.amazon.com/SQL-Server-Interview-Questions-Answers/dp/0985226854/ref=sr_1_1?ie=UTF8&qid=1448241316&sr=8-1&keywords=sql+server+interview+Questions+and+Answers
I prefer that candidates show me representative samples of their work rather than doing a task in an artificial and high-pressured environment. There’s no reason why someone can’t come prepared with at least an example or two of their prior work. That tells me what I need to know. Telling me how to reverse values or whatever is IMO pointless.