Database Screening Questions

April 30, 2009 at 1:58 pm (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, )


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:

  1. 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.
  2.  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.
  3. 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.
  4. 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.
  5. 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?
  6. 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.
  7. 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.
  8. 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.
  9. 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.
  10. 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.

32 Comments

  1. Gail said,

    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*

  2. scarydba said,

    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.

  3. Sankar said,

    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…

  4. Gail said,

    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.

  5. Gail said,

    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.

  6. SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA said,

    […] 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. […]

  7. scarydba said,

    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?

  8. scarydba said,

    Brent… It is a phone interview, so… Thank you for your time. You have a nice day.

  9. Gail said,

    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.

  10. scarydba said,

    I said it was “an” answer. I didn’t say it was “the” answer.

  11. Gail said,

    The “Not really” was in reply to my giving good stuff away. Or was that comment about the indexes?

  12. Sankar said,

    Gail,

    I couldn’t get all 7 yet.

    CI, NCI, Full-text Index, XML Index, Spatial Index. What abt the other 2?

  13. DataGeek said,

    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

  14. Adam Machanic said,

    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 :-)

  15. Gail said,

    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.

  16. Adam Machanic said,

    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]?

  17. Adam Machanic said,

    Filtered? I’d left that one out before but I suppose it should be included. I think a heap is a better argument :-)

  18. Gail said,

    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.

  19. Adam Machanic said,

    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!

  20. Gail said,

    :-D

  21. scarydba said,

    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).

  22. Gail said,

    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.

  23. Gail said,

    Typo. First line should read “I never asked for 7 types”

  24. scarydba said,

    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.

  25. Gail said,

    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.

  26. Vinay said,

    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

  27. Lynn Pettis said,

    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?

  28. scarydba said,

    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.

  29. geekgirl said,

    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

  30. scarydba said,

    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.

  31. Log Buffer #145: a Carnival of the Vanities for DBAs | Pythian Group Blog said,

    […] 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 […]

  32. Peso said,

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: