November 12, 2012 at 9:20 pm
Comments posted to this topic are about the item Transactions in T-SQL
November 12, 2012 at 11:54 pm
Lots of reading 🙂
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 13, 2012 at 12:58 am
Koen Verbeeck (11/12/2012)
Lots of reading 🙂Thanks for the question.
Agree - good question though.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 13, 2012 at 1:29 am
awesome read. 🙂
(there were some statements where I guess were added to create extra confusion... OR to make it more interesting... 😉 :rolleyes:)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 13, 2012 at 1:44 am
This was removed by the editor as SPAM
November 13, 2012 at 2:12 am
Rather complicated!
But easy to guess the right answer, as there had to be at least 2 rows in #temp (the 2 inserted outside the loop) which ruled out half the answers; and the loop was either going to insert 6 rows or none, which ruled out the other.
November 13, 2012 at 2:38 am
Thanks for the question.
Got it wrong because I missed the last INSERT statement after the WHILE LOOP.
If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.
A much more readable example that also tests the users knowledge of transactions could have been
create table #t1(id int)
insert into #t1 values(1)
begin tran t1
insert into #t1 values(2)
begin tran t2
insert into #t1 values(3)
begin tran t3
insert into #t1 values(3)
if @@trancount>0
rollback tran
if @@trancount>0
commit tran t2
if @@trancount>0
rollback tran t1
select * from #t1
November 13, 2012 at 3:10 am
Nils Gustav Stråbø (11/13/2012)
Thanks for the question.Got it wrong because I missed the last INSERT statement after the WHILE LOOP.
If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.
A much more readable example that also tests the users knowledge of transactions could have been
create table #t1(id int)insert into #t1 values(1)
begin tran t1
insert into #t1 values(2)
begin tran t2
insert into #t1 values(3)
begin tran t3
insert into #t1 values(3)
if @@trancount>0
rollback tran
if @@trancount>0
commit tran t2
if @@trancount>0
rollback tran t1
select * from #t1
the point here is.. once you check the tran count and you roll back them - it rolls back all of them... so there is no point to check the transaction again and commit it, as it never going to be greater than 0 after rolled back, in your example you created the transaction each one manually and the same is achieved via while loop in the Qtod, and there were some statements which adds kind of interest to the analysis where the making a choice going to take a while.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 13, 2012 at 3:32 am
Godd Question.
I did a big oops though, missed the first insert before the loop, so my second count was always out. Must rememeber to drink cofee before QOD...
Rodders...
November 13, 2012 at 3:39 am
Raghavendra, exactly. Keep it simple. That way you'll be able to test the reader's knowledge about transactions. No need to add a lot of extra noise.
November 13, 2012 at 4:11 am
Nils Gustav Stråbø (11/13/2012)
Raghavendra, exactly. Keep it simple. That way you'llbe able to test the reader's knowledge about transactions. No need to add a lot of extra noise.
I agree with that Nils. 🙂
(Simple is best... but as you know these Qtods can be tricky and it cannot meet the standards of acceptance to all so it is hard to form a question which is easily known to everyone, I just tag along and learn stuff. Actually not sure if this applies to all, but for me these noises are the best part where it makes the learning more fun/interesting and also it helps me to understand the (same) problem again in different way (if there's a chance) ... It can be pain sometimes but this pain makes us stronger... like a challenge.)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
November 13, 2012 at 5:16 am
Good question.
I got it wrong through sheer carelessness - read too quickly, and failed to remember when I got to the end that the very first insert is before the first transaction begins so that it isn't rolled back and it contributes a row to the final total.
The mixed style of some DML in explicit transactions and some not is something I rather dislike, since results are going to be different according to whether implicit transactions are off or on. There was no need, of course, to specify that they are off in this question since (a) implicit transactions are off by default (autocommit is the default transaction mode for DML outside explicit transactions), so that's what we should assume, and (b) none of the answers is correct for the case when implicit transactions are on, so thinking it's on wouldn't point at a wrong answer; but the question could form the basis of a nice fun demonstration of the discrepancies that can occurr when a chunk of code is called sometimes with autocommit on and sometimes with implicit transactions on.
Tom
November 13, 2012 at 5:47 am
Great question! I like this type of question that needs some research and reading for some of us. Keep them coming.
November 13, 2012 at 6:58 am
Good question. Got it wrong though, missed the first select..into statement before the while loop.
November 13, 2012 at 7:21 am
Today must be my lucky day. Saw the question, decided it was too long & complicated, guessed at random, got it right. Now off to buy a lotto ticket 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply