I got burned today at a SQL Server Interview!

  • Mark Markov

    Mr or Mrs. 500

    Points: 592

    The guy asked me:

    What is an index? --> I answered fine and said there's clustered and non-clustered.

    What is the difference? --> I answered fine and said clustered actually sorts the records and non-clustered stores pointers to the records.

    He said: Which one is better? ---> umm... I didn't know what to say

    He said: If you need to do a lot of SELECT, would you use a clustered or non-clustered index? --->> umm.. I didn't know again!

    He said: If you need to do a lot of INSERT, would you use a clustered or non-clustered index? ---> umm.. I don't know again!

    How would you answer to the last 3 questions??

    You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!

    Also, if anyone keeps a list of interview questions, I got burned on "can @@RowCount be set or is it read-only?" and "what is the difference between a primary key and a unique key?"(something about NULLS!)

  • Grant Fritchey

    SSC Guru

    Points: 395264

    Mark Markov (2/28/2008)


    He said: Which one is better? ---> umm... I didn't know what to say

    He said: If you need to do a lot of SELECT, would you use a clustered or non-clustered index? --->> umm.. I didn't know again!

    He said: If you need to do a lot of INSERT, would you use a clustered or non-clustered index? ---> umm.. I don't know again!

    How would you answer to the last 3 questions??

    You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!

    Also, if anyone keeps a list of interview questions, I got burned on "can @@RowCount be set or is it read-only?" and "what is the difference between a primary key and a unique key?"(something about NULLS!)

    Which one is better really depends on what we're talking about. For selecting an individual row, a covering non-clustered index, according to Itzik, is the fastest way to go, period. For selecting sets of data, clustered indexes generally perform faster. They honestly have different functions. The clustered index acts as a storage mechanism, as well as a retrieval mechanism where as the non-clustered index is primarily a retrieval mechanism.

    If you had to do lots of inserts, you'd want to test this, but you either want only a clustered index or possibly no index at all. I depends on your data, but the general rule is, a clustered index on every table.

    There's not much difference between a primary key & a unique key except that, you can only have one primary key and you can have a number of unique keys and the primary key will not allow nulls where as the unique key, depending on how its structured, does. There's more discussion here too.

    Do I get the job or do you want more detail?

    One piece of advice, I suspect these are meant to be open-ended questions, questions that elicit, not a yes or no, positive or negative, 42, style of response, but instead spark a discussion as a way of determining how much depth of knowledge you have. You're not likely to get an answer here that you can carry into an interview as a quick way to answer these kinds of questions. My favorite question to weed out the weak is "what's the difference between a clustered and non-clustered index." Once they get past that, I give them the real question, "So, a user calls and says the database is running slow. What do you do?" That's when things get fun in the interview or I say "Thanks for your time, our HR people will be in touch."

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 993753

    I'm curious, Mark... what position were you applying for?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993753

    One piece of advice, I suspect these are meant to be open-ended questions,

    I suspect they're really meant to find out if you actually know SQL Server or not. 😛 I've had folks that claim to be a "9 out of 10 in SQL server" right on the resume... one of them couldn't tell me how to get the current date and time in a query... the answer was "Dunno, we always used the GUI for that." S-w-e-e-e-e-t... just what I need for an SQL Developer...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Grant Fritchey

    SSC Guru

    Points: 395264

    Yeah, we had a guy who said he was a 10 on internals. We asked him how an index was stored... "In the database."

    Ta-Da!

    Thanks for playing. Our parting contestants will receive NOTHING! :hehe:

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Mark Markov

    Mr or Mrs. 500

    Points: 592

    Dear Grant Fritchey! You have helped a lot with explanation of indexes!!! Now I know what to tell them if I ever get lucky again to get an interview!:D

    Could you please please tell me what you would expect to hear in response to your question "The database is running slow. What do you do?" I am not asking that you write a novel here (I know it's a lot of information) but if you can tell me the most important points you would expect to hear I would really appreciate it! I think one way is open SQL Profiler and see who is running what queries but I am not sure... Thanks!

  • Mark Markov

    Mr or Mrs. 500

    Points: 592

    Jeff Moden (2/28/2008)


    One piece of advice, I suspect these are meant to be open-ended questions,

    I suspect they're really meant to find out if you actually know SQL Server or not. 😛 I've had folks that claim to be a "9 out of 10 in SQL server" right on the resume... one of them couldn't tell me how to get the current date and time in a query... the answer was "Dunno, we always used the GUI for that." S-w-e-e-e-e-t... just what I need for an SQL Developer...

    Even I know! Even I know! It's GetDate() 🙂

  • Grant Fritchey

    SSC Guru

    Points: 395264

    I'm mainly interested in hearing how they're going to troubleshoot stuff. I want to hear what they're going to do, how, when... I've had interviewees that insist the only way to find out what is running slow on the server is to grill the end-users or the developers to find out what they did or what they wrote instead of, oh, I don't know, connecting to the bloody machine and seeing what's happening. Yes, Profiler, then perfmon, sp_who2, Activity Monitor, error logs, wait states, locks... We'll talk. I'll get an idea of knowledge & experience. That's the point. Again, you have to get past the silly trivia contest to get to this question (and other open ended questions, I'll start drawing schema's on the white board and asking you to stub out queries, interviews are fun) like "tell me how many recovery models a database can have and what are the differences between them." I hate those questions, but they're out there to weed out the pretenders.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    Grant is right. One thing that helps is if you visualize your indexes a bit.

    The leaf pages of clustered indexes will contain the full row data, the leaf pages of nonclustered indexes

    will contain a row locator (either a row ID in case of heaps, or the cluster key)

    When you look up an individual row based on an index, you need to start at the root of the index's B-tree, and choose the appropriate child. In case of clustered index you will get to the row data at the end,

    in case of a non-clustered index you will get the row locator, so you will need to find the contents of this row later. If it was a clustered table, that means once again traversing an index structure (this time the clustered index). If it was a heap, then it is just lookig up the relevant database page, which is fast.

    Every level of an index's B-tree is also doubly linked, so for ranges you can follow these links.

    If you imagine these structures it is usually much easier to answer questions like you were asked 🙂

    PS: An interview is a two way process: Do ask them questions too 🙂

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Mark Markov

    Mr or Mrs. 500

    Points: 592

    Grant Fritchey (2/28/2008)


    I'm mainly interested in hearing how they're going to troubleshoot stuff. I want to hear what they're going to do, how, when... I've had interviewees that insist the only way to find out what is running slow on the server is to grill the end-users or the developers to find out what they did or what they wrote instead of, oh, I don't know, connecting to the bloody machine and seeing what's happening. Yes, Profiler, then perfmon, sp_who2, Activity Monitor, error logs, wait states, locks... We'll talk. I'll get an idea of knowledge & experience. That's the point. Again, you have to get past the silly trivia contest to get to this question (and other open ended questions, I'll start drawing schema's on the white board and asking you to stub out queries, interviews are fun) like "tell me how many recovery models a database can have and what are the differences between them." I hate those questions, but they're out there to weed out the pretenders.

    Mr. Grant Fritchey,

    If I run into you, the interview will be over in 5 minutes and your HR people will get back to me (next lifetime!)

    But... I noted down every word you said. The most important thing is that I WANT to learn, right??

    He also asked about deadlocks and why they occur, I mumbled something about isolation levels and he seemed "somewhat satisfied". Do you ask this too?

    Do you ask design questions such as Ralph Kimball methodology, star, snowflake, etc.? Normalization? I just finished Ralph Kimball's Data Warehouse Toolkit and I was prepared for that type of questions but he did NOT ASK ANY thereby not even opening the door for any such discussion =((

    P.S. Simple, Full and Bulk-logged? It's easy when I got Books Online in front of me :D:D

  • Grant Fritchey

    SSC Guru

    Points: 395264

    Oh yeah, we'll ask about deadlocks too. Isolation levels would be a grossly inadequate answer, just so you know.

    The thing is, we seldmon hire for entry level dba positions. Much more often we grow those inside the company. So when we're out trying to hire, even for consultants, we need people who really know what they're doing. I mean, we don't expect to get Jeff Moden in every interview (although, that'd be GREAT). When you say you know SQL Server, there is an expectation that means deadlocks, indexes, TSQL syntax, backups ... you kinow, the basics. If you say you know it really well, then I'm going to look at that as a learning opportunity for myself. I'll start presenting you with problems I've run into in recent weeks, solved or not. If you're pitching in, teaching me new tricks, you're hired. If you're looking at me like I just sprouted a weed out of one ear... "Our HR people will be in touch."

    I couldn't answer questions intelligently about star schema's, but then I'm not about to claim that I know anything about them.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004424

    When I'm interviewing, I ask questions on any or all of the below topics, depending what position I'm interviewing for.

    Indexes - types, differences.

    Statistics - what, why

    Database corruption - how to find, how to fix

    Backups - types

    Recovery modes - impact on recovery, backups

    Restores - How, what order, common errors

    Deadlock - causes, solutions (no, isoaltion levels aren't a cause or a solution)

    Optimisation - how, methods

    Performance monitoring - what do you look for.

    Database theory - assorted questions

    T-SQL - assorted (though I leave most of that to the technical test)

    Like Grant, I'm not hiring entry level. I'm currently looking for someone to assis me with optimisation and complex problems. When I do interview entry levels, I'm much, much more lenient.

    That said, if you say you're an expert on SQL, expect me to ask expert level questions. If you say you're average, I'll be asking average level questions.

    As for your initial question, I'd guess he was looking for Select - nonclustered and insert - clustered. It's a massive simplification, but....

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 993753

    Um... star schema's? Isn't there a sidewalk like that somewhere in Hollywood? 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Mark Markov (2/28/2008)


    You see, I don't really know SQL Server, I am just trying to memorize all the answers and get a job! :w00t: Please don't criticize me, I need to pay the mortgage!!!!!!!!!!!!!

    Are you being honest up front about your lack of knowledge?

    I'll sometimes recommend someone who knows little, but is intrested in learning. If, however, they've lied on their CV then there's no chance I'll recommend.

    Ay skilled interviewer will be able to tell memorised answers from actual knowledge in ust a couple of questions.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail Shaw

    SSC Guru

    Points: 1004424

    Jeff Moden (2/28/2008)


    Um... star schema's? Isn't there a sidewalk like that somewhere in Hollywood? 😀

    Might be. I know I've heard something about something like that. Will have to check in wiki. I know snowflakes are something found in the high mountains. 😉

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 358 total)

You must be logged in to reply to this topic. Login to reply