June 14, 2016 at 1:43 pm
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/
June 14, 2016 at 1:47 pm
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".
June 14, 2016 at 1:51 pm
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/
June 14, 2016 at 1:54 pm
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
June 14, 2016 at 2:06 pm
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".
June 14, 2016 at 2:24 pm
And here I was thinking of using a NOLOCK hint. Anyhow, both seem like bad ideas if the issues are not completely understood.
June 14, 2016 at 2:39 pm
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.
June 14, 2016 at 10:20 pm
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
Change is inevitable... Change for the better is not.
June 14, 2016 at 10:44 pm
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
Change is inevitable... Change for the better is not.
June 14, 2016 at 10:58 pm
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
Change is inevitable... Change for the better is not.
June 14, 2016 at 11:27 pm
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
June 15, 2016 at 7:37 am
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
June 15, 2016 at 8:20 am
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
Change is inevitable... Change for the better is not.
June 15, 2016 at 8:34 am
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.
June 15, 2016 at 8:39 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 61 through 75 (of 111 total)
You must be logged in to reply to this topic. Login to reply