With all the cool kids posting about beginners and interview questions, I thought I’d toss my favorites out there, from the brief-case gang point of view. These are the technical phone-screening questions I use after I look at a resume. There are only 10. They’re simple. Stupid simple. Silly even. Yet, I can count on eliminating 4 out 5 people who have a resume that looks like a qualified DBA. I’ve seen people with 10 years experience fail on these questions.
I’m only going to provide the questions. If you can’t find the answers on your own, you’re already disqualified:
- What is the difference between a clustered and non-clustered index?
No, don’t tell me that one is clustered and the other is not. I don’t need specific low-level information on this, just a demonstration of knowledge that the difference is understood. - Â What is the difference between a block (b – l – o – c – k) and a deadlock (d – e – a – d – l – o – c – k)?
And yes, I spell the words. I don’t want any chance of misunderstanding. And yet, most people carefully explain what a block is and then carefully explain what a block is again. - Can you tell me two of the three recovery models in SQL Server and what the difference between them is?
Again, I don’t need to know what happens differently inside of the checkpoint operation, just tell me what’s different. Yeah, I only ask for two since almost everyone only uses one of the two. - Can you tell me a few things that might cause a query stored in cache to recompile?
Let me tell you that, yes, they do. I’ve had several people argue with me on that question. - What do you think the query hint NO_LOCK might do?
This should be a give away. I’m not asking for specifics. I’m assuming you don’t know. Why would you say “I have no idea” to a question like this? - Can you tell me some of the various types of backups that are available in SQL Server?
If you give me three, I’ll be overjoyed. I need at least two. - How did error handling change in SQL Server 2005?
Note, not how do you write error handling based on the change in 2005, just, what was the change. I need to know you’re aware there was one. - Do you have any experience working with [latest hot topic] inside SQL Server?
Our latest is Microsoft Dynamics CRM. We’ve also asked the question about XML and other stuff. It’s just an attempt to understand you. Talk about what you know or don’t know. - Do you have experience with Version X of SQL Server?
Now I ask about 2008, but before I asked about 2005. “No” is a perfectly acceptable answer. “I’ve never heard of it” or “That’s not out yet” or “No, but I have lots of experience with 2009” are pretty much disqualifiers. Broke my rule about no answers there, but I hate seeing people get this one wrong. - You’re the DBA. The phone rings. One of the users is on the line. They say “The database is slow.” Then they hang up. What do you do?
My favorite was the guy who wanted to track down the user in order to get his name and his managers name and to fill out a series of forms before he’d even consider the technical aspects of the question. This is the only open-ended question I ask for screening.
Preparing this I went back through my notes. I keep notes on every interview. It’s creepy. Page after page of people who can’t answer even four of these questions. We only want you to correctly answer six before we bring you in for an interview.
So, if you’ve got five or ten years experience as a DBA and you think this was a tough quiz… time to evaluate what you’ve been doing. If you’re just starting out, here are some of the basics that it might be nice to know.
I asked someone question 7 (or very similar) once. The reply I got was “Error handling?” I followed up by asking “What does @@Error do?” Answer: “No idea”
They were applying for a senior SQL developer position and had 6 years experience developing on SQL Server.
I asked someone once how to deal with a torn page error. His CV stated he was a senior SQL Server specialist. His answer started “I fire up Visual Studio and start a new VB project”
I’ve seen a ‘senior SQL DBA’ with 4+ years experience get 0% for a written technical test in which the first question read “Write a query that returns the ID and name columns from a table called Person”
My variant on Q1 used to be ‘Name two types of indexes in SQL Server’. As of 2008, there are at least 5 (some would argue 7). I don’t think I’ve ever had someone who could name 2. Most can’t name 1.
Oh, and my answer for 10. Go back to the game I was playing. If it’s important they’ll call back.
*evil grin*
For you, I’d let that go on #10.
Thanks for the laugh.
I hate to say this, I haven’t had to deal with a torn page error. I’d be hitting the books really hard & fast if I had one.
Gail,
>>As of 2008, there are at least 5 (some would argue 7)
Apart from CI/NCI, Are you referring to Composite Index/Unique Index/covering index/Included cols or filtered indexes, xml indexes, partition indexes, spatial indexes etc…
Composite and covering are not types of indexes, they are descriptions of indexes and unique is an attribute of an index, though you could argue for it being a type.
You don’t, for example, write CREATE COVERING INDEX or CREATE COMPOSITE INDEX.
I’m also not talking about indexes on partitions as partitions are, in the depths of the engine, much the same as tables
No, for those 5, I’m talking about 5 types of indexes that are different in their architecture. That is not true for composite or covering. It’s arguable for unique and unique is one of those 2 that I said you could argue for.
Yeah Grant, but you’re not going to start talking about writing VB for a torn page. I told the guy that it was a form of corruption, so that should be at least a hint to him.
Any time I’m doing interviews the answer “I consult a colleague/a forum/google” is more acceptable than “I don’t know” and way more acceptable than the wrong answer given with complete confidence.
[…] Interview Questions for Any DBAs – by Grant Fritchey. I would hate to answer the difference between a block and a deadlock because I can’t do it without using a whiteboard. I dunno what it is, but there’s some concepts I just can’t explain without a marker in my hand. It’s not like I do a good job of drawing, either. […]
Gail… shhhh! You’re giving away all the good stuff for free.
But seriously, I’m OK with “I don’t know” as an answer. My next question will be “So, how would you find the answer?” so you had best have a resource or three.
I remember one interview, a while back, the person being interviewed kept railing against Google as a way to solve problems in SQL Server. Personally, I thought, hey, whatever works, right?
Brent… It is a phone interview, so… Thank you for your time. You have a nice day.
Not really. If “I’ll google it” is the answer to a good percentage of the questions, they’re still on the no-hire list.
I said it was “an” answer. I didn’t say it was “the” answer.
The “Not really” was in reply to my giving good stuff away. Or was that comment about the indexes?
Gail,
I couldn’t get all 7 yet.
CI, NCI, Full-text Index, XML Index, Spatial Index. What abt the other 2?
Interview question must go on from bottom up. And it is a good manner to explain why are you asking this and getting harder slow as the dba answers it. Same as who wants to be a millionair type of questions. It is not good to judge time. It is better to judge by years of experience or years in job. People in H1B has to be at job till they get their green card. So he will say I am a dba for past 7 some years. He might be just sitting or supporting coz he cant quit. It is good to ask why they were in that job for that many years besides asking what exactly they did. I hate to see someone ask me dummy question even though it is silly. So it is better to say i am asking this as a start… bla bla
Gail,
What are the 7 index types you’re referring to that are ACTUALLY “different in their architecture” (your words)?
Let’s start at the top: clustered and nonclustered indexes are B*Trees with intermediate and leaf (data) pages that share exactly the same on-disk structures and use exactly the same algorithms. So those are one type if we’re talking about architecture. UNIQUE is certainly not at all different, as all indexes are unique whether or not you explicitly use that option; if you don’t qualify it a hidden column called a “uniqueifier” is added that guarantees uniqueness.
So that leaves us with the “special” index types (unless you’re thinking of something totally different, but I can’t think of what that would be). XML indexes are nothing more than clustered and nonclustered indexes under the covers, so we’re still on one type. FTS in SQL Server 2000/2005 is certainly a different architecture, but in 2008 with iFTS we’re back to the B*Tree paradigm and it’s architecturally no different from a straight indexing point of view than any other index. Spatial indexes are a bit of a black box for me so I can’t comment there. I suspect it’s more of the same but I’ll give the benefit of the doubt.
So in my opinion there are at most two types of indexes that are actually different architecturally, in SQL Server 2008. Feel free to let me know where I’ve gone wrong 🙂
You’re going far deeper than I was thinking or would ever be asking in an interview. Come back up a couple levels. 😉
I know xml and spatial are b-trees deep in the engine, but, iirc, they don’t look exactly like a standard NC index (the hidden tables for one thing)
Oh, and I asked for 5 types, not 7. I said there are 2 more that you can argue for, unique is one of those, I don’t consider it different, if someone in an interview can argue persuasively that is and there arguments aren’t completely false, I’m happy.
Clustered, nonclustered, XML, spatial, full-text ?
+ UNIQUE (eh!) + [now you have me stumped — I guess one could argue that a heap is a form of an index since the engine can do lookups by RID]?
Filtered? I’d left that one out before but I suppose it should be included. I think a heap is a better argument 🙂
Filtered.
Again I don’t consider that different, it’s just a NC index on a portion of the table, but if someone can convincingly argue that it is, they get credit (first for knowing about it, second for a convincing argument)
Hell, if someone knows enough about indexes to make any form of argument for or against I’d probably have hired them on the spot.
I have had someone try ans persuade me that a unique index was fundamentally different from a non-unique one. Not in an interview, usergroup I think it was.
I think, of the times I’ve asked for types of indexes in interviews, I’ve had one person who could name clustered. Most can’t manage that. No one’s ever mentioned any of the others (ignoring spatial, last interview I did was pre SQL 2008).
And that’s not interviewing for admin people, the job positions were for SQL developing or performance tuning.
Well it sounds like you got exactly what you needed. I mean, who would ever use an index for performance tuning purposes? Indexes only make things WORSE by slowing down all data modification! Drop the indexes and you’ll free up server resources to make the queries FASTER!
😀
Adam,
Thanks so much for the GREAT advice. Holy cow. All the time I’ve wasted in trying to find the right index when I should have been actively deleting them. I’ve just finished nuking the indexes on all the production systems. I now waiting for the shrieks of joy to resound round the building…
Sorry I’ve been letting you guys run without input. I was camping with the scouts all weekend. I’m really playing catch-up.
DataGeek: I agree. However, I do ask these questions on a phone interview so that we can avoid the face-to-face if you’re not past entry level on your knowledge set. If I had to ask these of Adam or Gail, I’d preface with the fact that, for legal purposes, I have to ask the same questions of them that I ask of everyone else. Then we’d burn through them, I’d learn something (like I didn’t know that the new iFTS was stored in a b-tree) and then we’d bring them in for the real interview. The purpose of these questions is to screen, meaning to sift out or filter the job candidates from those claiming knowledge and those having knowledge. Note, the questions stay away from syntactical memorization. That’s not what I’m looking for. I’m looking for knowledge & understanding.
Gail & Adam: I agree with Adam, Gail. I couldn’t come up with seven distinct ones either. However, I’m assuming you just want this as a means of communication to talk about methods & practices, etc. and you wouldn’t ding me if I didn’t list UNIQUE as a seperate index type (I hope).
I asked for 7 types. I said 5, with 2 more that I don’t consider personally to be distinct types (unique being one of those), and Adam got them perfect, as soon as he came up from the innards of the engine 😉
All I ever ask for in an interview, as I said in my first comment, is 2 and all I want is clustered and nonclustered. If someone can mention one of the others, great. It’s above what I would expect. It shows that the candidate is familiar with the less commonly used areas of the product
If someone knows enough about indexes to make a case that unique should be considered a type, that counts in their favour even though I don’t agree. Provided that their argument has some basis in fact.
Considering that I’ve never had someone who could even name clustered and nonclustered, I’d be an idiot to ask for 5, 7 or 42 and expect the entire list rattled off.
Typo. First line should read “I never asked for 7 types”
Sorry Gail. I really wasn’t trying to put words in your mouth there. I also don’t disagree with you. I’d still hate to get interviewed by you for a job.
My former colleagues used to say much the same thing.
Considering the skill level of people in my area, I’d hire you on the spot, no questions asked. Not that I’m in a position to do so any longer.
someone should not underestimate others… not all H1B are just for supprts…. everyone has knowledge but does matter who is good in what…..
Thank you.
Vinay
Okay, good questions Grant. I think I could answer all 10 of your questions, and I think my answer to #10 would be similar to Gail’s.
As to Gail’s question regarding index types, I came up with the following: clustered, nonclustered, full text, xml, and spatial. That totals 5, so I did I do Gail?
I would have been shocked if you didn’t get them. These are meant to seperate the wheat from the chaff and they really work.
Microsoft gives a nice little answer to your question 4:
The following are the conditions under which a stored procedure is recompiled:
An index on a referenced table is dropped.
The table is altered using ALTER TABLE.
A rule or default is bound to the table or column.
The stored procedure has been flagged for recompilation by executing sp_recompile on any referenced table.
The stored procedure is executed using the WITH RECOMPILE option.
The stored procedure is created using the WITH RECOMPILE option.
All copies of the execution plan in cache are currently in use.
FYI
Interestly enough, the list supplied by MS leaves out at least one big reason for why a stored procedure might recompile. Still, if you got all, or even a substantial portion, of those, you certainly pass that question.
[…] the Home of the Scary DBA appeared something along similar lines, Database Screening Questions, picking up a thread from Brent Ozar and the SQL Batman on newbie DBA […]
For #4, I can think of these other scenarios to make a plan recompile (or at least be dropped).
Change of an underlying schema.
Alter or edit an index.
Updates statistics
Changes selectivity with insert, delete or update (an indexed column).
Changes a SET option.
Use of SET DATEFIRST.
Changes CURSOR options.
Restarting the server should also recompile the SP(s) when they are (re)executed for the first time. I guess so.
Recompile is not the word I’d use there. It’s a first time compile for everything.
[…] really like the questions that Grant Fritchey has on his blog. To get in the door for an in-person interview, the candidate needs at least 5 of these correct. […]
I actually printed out your questions and used them on several interviews. Then, I took it a small step further.
In my own job-hunting interviews, I usually do a brain dump when I get home and research all the questions that were asked of me. I make it a point to know the answer or at least have some level of understanding.
When we hire DBAs we usually do a phone interview first. I asked most of the questions from the your list. He got most of them wrong but the applicant made a better than good effort in his attempt to answer the questions (from me and the other DBAs). So we brought them in-house for a face to face.
I asked the EXACT same questions, and I got the same exact wrong answers. Not what I expect from a DBA.
This is how I weed them out if they get past the first round.
Oooh, I like that. Asking the questions a second time. Nice mechanism for eliminating cheat sheets.
[…] not a fan of trivia style interview questions. Yes, I ask a few because you have to in order to immediately eliminate the completely unqualified applicants. Even […]
<>
Sorry to bring up old topic from 2009, but I was wondering: sounds like your comment is ironic, but isn’t it actually the correct advice? In my company they teach that when you load data via ETL, you should not have any indexes on that table then the load is faster. Then, have automatic process move the data into a different table (OLAP) and have plenty of indexes on it, then users query that table and all reports run against that table. Is this correct?
Hi Oksana,
The short answer, it depends. Updating the indexes when you’re inserting data certainly adds to the overhead. So, dropping indexes that are not needed for data loading and then recreating them after the data load is complete is a common approach. But, I would argue that a blanket “not have any indexes on the table” is a poor approach that would lead to problems. Instead, I would say, let’s not have any indexes that are not needed for the data processing. One of the indexes you would leave in place is the cluster. Otherwise, you’re rearranging the data after you insert it, which is certainly going to be a performance hit.
[…] 1) One mistake I made job searching was not checking my contacts for people that already worked at companies I was applying to. After interviewing at one place, a LinkedIn search revealed that the leader of my local SQL Server User’s group worked at the same place. Luckily, I ended up getting that job, but knowing someone already established there may have helped my effort. 2) Job boards and forum postings didn’t get me anywhere. The only things that got me interviews were personal referrals and recruiters. The job I landed I found out about from a recruiter that approached me on LinkedIn. I didn’t try applying for jobs through LinkedIn, but recruiters definitely find you on there. 3) I went out in the Winter and updated my Microsoft SQL Server certification, which I was really proud of attaining. However, no one I applied to or interviewed with even mentioned the fact that I had a MCSE. Of course, it could have helped me to get an interview without me knowing, but it seems like it would have been mentioned. 4) But the one thing that every single company I approached mentioned was my blog. 5) I was really concerned about the technical interviews. I made sure to study up for those, finding blog posts with interview questions and answers. Here are some resources I used: Brent Ozar SQL Server Central Interview Questions SQL Server Central SQL Developer Questions and Answers Scary DBA […]
[…] Database Screening Questions […]