Interview Questions

  • ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    Well you got me. I don't know of a way a single update statement could partially commit. That violates Atomicity unless there is something else in your question I am not getting.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (6/14/2016)


    ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    Well you got me. I don't know of a way a single update statement could partially commit. That violates Atomicity unless there is something else in your question I am not getting.

    Yep, it does indeed violate Atomicity, and, yep, you are getting the q. A bit of an interesting "quirk", let's say, in SQL Server.

    Edit: I don't expect the person to be able to come up with it, but I am interested in their level of curiosity about the answer when revealed.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (6/14/2016)


    Sean Lange (6/14/2016)


    ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    Well you got me. I don't know of a way a single update statement could partially commit. That violates Atomicity unless there is something else in your question I am not getting.

    Yep, it does indeed violate Atomicity, and, yep, you are getting the q. A bit of an interesting "quirk", let's say, in SQL Server.

    Edit: I don't expect the person to be able to come up with it, but I am interested in their level of curiosity about the answer when revealed.

    Well I am curious. Spill the beans. 😛

    --EDIT--

    Feel free to PM if you don't it want your question answered our in the real world.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    I know the answer you want, but I'll take a stab at another possible answer you're not expecting. Let's say we're updating a distributed paritioned view containing a union of multiple remotely linked tables and something goes haywire with the distributed transaction coordinator.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • It's my understanding -- although I can't construct code right now that confirms it -- that:

    SET LOCK_TIMEOUT

    can cause that type of behavior. I had faith in that claim because Kalen Delaney mentioned it in one of her books, even after SQL 2008 came out (don't know about later SQL versions):

    The LOCK_TIMEOUT setting might sound like just what you've been waiting for, but use

    it with extreme caution. If a session stays blocked for longer than the LOCK_TIMEOUT

    setting, SQL Server generates a lock timeout error. This error doesn't automatically roll

    back a transaction. Therefore, when SQL Server reaches its lock timeout value, it stops

    trying to modify rows in the current table and moves on to the next statement. Instead of

    the transaction being an atomic, all-or-nothing operation, we might be left with part of

    the transaction incompletely executed.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • And here I was thinking of using a NOLOCK hint. Anyhow, both seem like bad ideas if the issues are not completely understood.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    Intriguing...I'd be interested in a repro of that for an UPDATE in autocommit, as I've never seen that behavior.

    I know that if you have multiple statements within an explicit transaction, and one of the statements fails because of a lock timeout, the transaction will happily commit the changes from the other statements despite one of the statements failing.

    That would also seem to violate atomicity (isn't really special, though; it's like other such run-time errors that need special handling or XACT_ABORT ON), but is slightly different than a single UPDATE statement partially completing. That would be fascinating indeed, and much more interesting than what I described. 🙂

    Cheers!

    EDIT: Fixed a typo and clarified some wording.

  • GSquared (6/14/2016)


    Jeff Moden (6/10/2016)


    Eric M Russell (6/10/2016)


    Eric M Russell (6/7/2016)


    They're asking the wrong type of questions for a job interview.

    I'm not saying that string manipulation is a bad question. It's just that for a one hour interview I'm splitting my time between one or two other interviewers, and I only get a chance to ask maybe a half dozen questions. So, I'd rather rather ask a quesion similar to one of the examples I provided earlier that digs deeper into their past experience. There are plenty of folks who actually good T-SQL coders, but for whatever reasons they can't understand the questions we're asking or can't "think on their feet". If I ask someone "Explain the difference between locking, blocking, and deadlocking" or "Explain why it's potentially a bad idea to place a function in a WHERE clause expression", and they fumble without providing an coherent intermediate level answer, then at that point I lose interest can couldn't care less whether they can slice and dice strings. I don't interview interns.

    Ah... got it and now I understand where you're going with all of this and, in that case, I absolutely agree.

    Most of the people I've had the great displeasure of interviewing don't make it past the 15 minute mark. I just don't have the time to dedicate past the first 3 questions if they don't even come close to correct answers on those first 3 questions. And, remember, the first question is always "How do you get the current date and time using T-SQL?" whether it's for a DBA or an Developer position. The other two vary but are never in the "SQL Ninja" category.

    It's been really slim pickings here in this part of the U.S.A.

    And the reply should be "Do you mean current server-time, or current UTC, or some sort of localized current time?"

    Clear requirements make better code.

    My answer then would be "Yes... all of them... and why you would use one over the other"?

    Instead of answering a question with a question, you should identify the things that will do all the things you just mentioned since you're the one that brought them up in the interview.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eric M Russell (6/14/2016)


    You'll rarely ever be asked pop quiz style questions like "What is a clustered index?" or "How do you get the current date and time?", unless part of your job involves mentoring an intern.

    So you would hire someone as a Senior Developer that could do the join in their head but knew nothing of clustered indexes and didn't know how to get the current date and time? And, no... not being sarcastic. I've actually had candidates that could erg out join answers but knew nothing of the fundamentals to make them work in an efficient manner. When I'm hiring someone for a Senior position, I want them to know the answers to such "pop quiz style" questions... and they'd better be able to answer the other questions those 2 simple questions segue into.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher (6/14/2016)


    My advanced knowledge, "tricky" q is:

    In autocommit mode, when can an UPDATE statement partially complete, some UPDATEs made, some not, where SQL does not roll it back?

    Edit: And assuming you don't have CATCH logic to handle the issue.

    In the last dozen years, I've only gotten to "tricky" (actually, never "tricky" questions... just advanced questions) questions with 3 people. Most of the rest didn't know how to get the bloody current date and time and those that did manage to squeak out only the most obvious answer instead of the 6 available crashed and burned on other basic questions like "Tell me what you know about Clustered Indexes".

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/14/2016)


    other basic questions like "Tell me what you know about Clustered Indexes".

    Group of indexes merged into a cluster?

    Correct?

    _____________
    Code for TallyGenerator

  • Jeff Moden (6/14/2016)


    GSquared (6/14/2016)


    Jeff Moden (6/10/2016)


    Eric M Russell (6/10/2016)


    Eric M Russell (6/7/2016)


    They're asking the wrong type of questions for a job interview.

    I'm not saying that string manipulation is a bad question. It's just that for a one hour interview I'm splitting my time between one or two other interviewers, and I only get a chance to ask maybe a half dozen questions. So, I'd rather rather ask a quesion similar to one of the examples I provided earlier that digs deeper into their past experience. There are plenty of folks who actually good T-SQL coders, but for whatever reasons they can't understand the questions we're asking or can't "think on their feet". If I ask someone "Explain the difference between locking, blocking, and deadlocking" or "Explain why it's potentially a bad idea to place a function in a WHERE clause expression", and they fumble without providing an coherent intermediate level answer, then at that point I lose interest can couldn't care less whether they can slice and dice strings. I don't interview interns.

    Ah... got it and now I understand where you're going with all of this and, in that case, I absolutely agree.

    Most of the people I've had the great displeasure of interviewing don't make it past the 15 minute mark. I just don't have the time to dedicate past the first 3 questions if they don't even come close to correct answers on those first 3 questions. And, remember, the first question is always "How do you get the current date and time using T-SQL?" whether it's for a DBA or an Developer position. The other two vary but are never in the "SQL Ninja" category.

    It's been really slim pickings here in this part of the U.S.A.

    And the reply should be "Do you mean current server-time, or current UTC, or some sort of localized current time?"

    Clear requirements make better code.

    My answer then would be "Yes... all of them... and why you would use one over the other"?

    Instead of answering a question with a question, you should identify the things that will do all the things you just mentioned since you're the one that brought them up in the interview.

    Really? You want all of the infinite ways to get the current date and time in a computer system? And asking you to clarify a question is forbidden?

    I sincerely hope I never get stuck in that interview. Not because I can't answer the questions, but because (per description) it sounds painfully awkward on a human level.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (6/15/2016)


    Jeff Moden (6/14/2016)


    GSquared (6/14/2016)


    Jeff Moden (6/10/2016)


    Eric M Russell (6/10/2016)


    Eric M Russell (6/7/2016)


    They're asking the wrong type of questions for a job interview.

    I'm not saying that string manipulation is a bad question. It's just that for a one hour interview I'm splitting my time between one or two other interviewers, and I only get a chance to ask maybe a half dozen questions. So, I'd rather rather ask a quesion similar to one of the examples I provided earlier that digs deeper into their past experience. There are plenty of folks who actually good T-SQL coders, but for whatever reasons they can't understand the questions we're asking or can't "think on their feet". If I ask someone "Explain the difference between locking, blocking, and deadlocking" or "Explain why it's potentially a bad idea to place a function in a WHERE clause expression", and they fumble without providing an coherent intermediate level answer, then at that point I lose interest can couldn't care less whether they can slice and dice strings. I don't interview interns.

    Ah... got it and now I understand where you're going with all of this and, in that case, I absolutely agree.

    Most of the people I've had the great displeasure of interviewing don't make it past the 15 minute mark. I just don't have the time to dedicate past the first 3 questions if they don't even come close to correct answers on those first 3 questions. And, remember, the first question is always "How do you get the current date and time using T-SQL?" whether it's for a DBA or an Developer position. The other two vary but are never in the "SQL Ninja" category.

    It's been really slim pickings here in this part of the U.S.A.

    And the reply should be "Do you mean current server-time, or current UTC, or some sort of localized current time?"

    Clear requirements make better code.

    My answer then would be "Yes... all of them... and why you would use one over the other"?

    Instead of answering a question with a question, you should identify the things that will do all the things you just mentioned since you're the one that brought them up in the interview.

    Really? You want all of the infinite ways to get the current date and time in a computer system? And asking you to clarify a question is forbidden?

    I sincerely hope I never get stuck in that interview. Not because I can't answer the questions, but because (per description) it sounds painfully awkward on a human level.

    There aren't infinite ways in T-SQL. I will say that someone that expressed the outrage at a simple question as you just have wouldn't make it through one of my interviews. 😉

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/15/2016)


    GSquared (6/15/2016)


    Jeff Moden (6/14/2016)


    GSquared (6/14/2016)


    Jeff Moden (6/10/2016)


    Eric M Russell (6/10/2016)


    Eric M Russell (6/7/2016)


    They're asking the wrong type of questions for a job interview.

    I'm not saying that string manipulation is a bad question. It's just that for a one hour interview I'm splitting my time between one or two other interviewers, and I only get a chance to ask maybe a half dozen questions. So, I'd rather rather ask a quesion similar to one of the examples I provided earlier that digs deeper into their past experience. There are plenty of folks who actually good T-SQL coders, but for whatever reasons they can't understand the questions we're asking or can't "think on their feet". If I ask someone "Explain the difference between locking, blocking, and deadlocking" or "Explain why it's potentially a bad idea to place a function in a WHERE clause expression", and they fumble without providing an coherent intermediate level answer, then at that point I lose interest can couldn't care less whether they can slice and dice strings. I don't interview interns.

    Ah... got it and now I understand where you're going with all of this and, in that case, I absolutely agree.

    Most of the people I've had the great displeasure of interviewing don't make it past the 15 minute mark. I just don't have the time to dedicate past the first 3 questions if they don't even come close to correct answers on those first 3 questions. And, remember, the first question is always "How do you get the current date and time using T-SQL?" whether it's for a DBA or an Developer position. The other two vary but are never in the "SQL Ninja" category.

    It's been really slim pickings here in this part of the U.S.A.

    And the reply should be "Do you mean current server-time, or current UTC, or some sort of localized current time?"

    Clear requirements make better code.

    My answer then would be "Yes... all of them... and why you would use one over the other"?

    Instead of answering a question with a question, you should identify the things that will do all the things you just mentioned since you're the one that brought them up in the interview.

    Really? You want all of the infinite ways to get the current date and time in a computer system? And asking you to clarify a question is forbidden?

    I sincerely hope I never get stuck in that interview. Not because I can't answer the questions, but because (per description) it sounds painfully awkward on a human level.

    There aren't infinite ways in T-SQL. I will say that someone that expressed the outrage at a simple question as you just have wouldn't make it through one of my interviews. 😉

    The only reason I don't think I would not make it through one of your interviews is that no matter how much you assure me that there are no trick questions, I'll still make everything harder than it actually is. Just in my nature.

  • An interview is the one place where volunteering information can do nothing but good... if you know what you're talking about. When someone asks a simple question about how to get the current date and time in T-SQL, they can really start to strut their stuff by answering something like the following instead of asking for clarification on such a simple question...

    "It depends on what type of date/time you're after. The GETDATE() function would be one way but there are 6 functions in T-SQL specifically designed for this purpose. Some return the DATETIME datatype and some return the DATETIME2 data type. Some, like GETDATE(), return the local server time, some return UTC time, and some are used when time zones are important. I don't have all 6 functions committed to memory but I do know where to find them in Books Online."

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 61 through 75 (of 111 total)

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