Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

TRUNCATE in TRANSACTION Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 5:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:41 AM
Points: 907, Visits: 892
Hugo Kornelis (1/4/2011)
I replied wrong - I saw the rollback after the truncate, assumed the question intended to test my understanding that a truncate can be rolled back, and then replied. I had not seen the missing ALL after the UNION operators, nor noticed the duplicate row.

Whew! If I got it wrong for the same reason Hugo did, then I must be doing okay.

ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #1042321
Posted Tuesday, January 4, 2011 5:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 1,890, Visits: 3,472
Got it wrong because I misread the UNION for UNION ALL. I focused on the transaction rollback and therefore counted the number rows with 3 in it in the first insert statement.

Good question. A good reminder that assumptions seldom leads to any good!
Post #1042322
Posted Tuesday, January 4, 2011 6:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:17 AM
Points: 2,917, Visits: 2,537
Got it wrong, but learned something - which is the key

Hugo,
The knowledge tested is good, but the way the question has been built suggests to me that the author tried to trick people into overlooking key elements


Many have said, and usually do of most questions that the QOD's are tricks but I question the abilities of many. Don't take offense, but answer this (not necessarily to me) - a portion of many DBA's time is spent reviewing code from developers who have varing levels of expertise in writing SQL code. Isn't part of what we are supposed to be doing looking for things such as this?

I know, fine one to talk when I got it wrong, but...


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #1042341
Posted Tuesday, January 4, 2011 6:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
sjimmo (1/4/2011)
Got it wrong, but learned something - which is the key

Hugo,
The knowledge tested is good, but the way the question has been built suggests to me that the author tried to trick people into overlooking key elements


Many have said, and usually do of most questions that the QOD's are tricks but I question the abilities of many. Don't take offense, but answer this (not necessarily to me) - a portion of many DBA's time is spent reviewing code from developers who have varing levels of expertise in writing SQL code. Isn't part of what we are supposed to be doing looking for things such as this?

I know, fine one to talk when I got it wrong, but...

Agreed but normally when you are helping someone troubleshoot a problem, you're given the code and what the output is and what it should be. It's easy to figure out the problem that way.

On these QOTD's, we are given a subject that says one thing, leading us to believe that's what is being tested, and then when you look at the answer, it was all about something else completely different.

If we got the output provided to us and was asked to state why some of the records were missing, I bet more people would have figured out it was due to the "UNION" versus "UNION ALL" but that's not the way the question was presented.
Post #1042345
Posted Tuesday, January 4, 2011 6:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, November 27, 2014 4:23 AM
Points: 20,584, Visits: 9,624
Hugo Kornelis (1/4/2011)
I replied wrong - I saw the rollback after the truncate, assumed the question intended to test my understanding that a truncate can be rolled back, and then replied. I had not seen the missing ALL after the UNION operators, nor noticed the duplicate row.

The knowledge tested is good, but the way the question has been built suggests to me that the author tried to trick people into overlooking key elements. It would have been better to make two questions, one about rolling back after truncate table (with no other elements to confuse the reader), and one that uses UNION and inserts a duplicate row (again, with no other elements to distract the reader).



Ditto. learned nothing here. Moreover I "never" use unions to load data into table unless I'm creating test data... especially with 15 manuel inserts.

I think most unknown fact in this question is that the truncate will be rolled back. Not that there's a difference between union and union all.

I would rather have seen a question hammering on the former point rather than the latter.
Post #1042352
Posted Tuesday, January 4, 2011 7:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 10, 2014 12:05 PM
Points: 3,969, Visits: 3,648
I fell into the same trap. I overlooked the WHERE clause. Not sure I understand what the point of that was?
Post #1042427
Posted Tuesday, January 4, 2011 8:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 5, 2013 7:19 AM
Points: 69, Visits: 65
Hardy21 (1/4/2011)
cengland0 (1/4/2011)
Hugo Kornelis (1/4/2011)
I replied wrong - I saw the rollback after the truncate, assumed the question intended to test my understanding that a truncate can be rolled back, and then replied. I had not seen the missing ALL after the UNION operators, nor noticed the duplicate row.

The knowledge tested is good, but the way the question has been built suggests to me that the author tried to trick people into overlooking key elements. It would have been better to make two questions, one about rolling back after truncate table (with no other elements to confuse the reader), and one that uses UNION and inserts a duplicate row (again, with no other elements to distract the reader).


Exactly the same thing that I did. I looked at the subject of the QOTD and it said "TRUNCATE in TRANSACTION." I did notice it didn't say UNION ALL which I always do myself but I didn't scrutinize it enough to check for duplicates because I assumed I was being tested on the TRUNCATE command.
Same with me
Question is good but title is misleading.
I got it wrong but I like the way author wrote WHERE condition - value IN (column1, column2).


Was just about to say exactly the same myself. Clearly a lesson to be learned, but the author has been particularly sneaky using that heading!
Post #1042500
Posted Tuesday, January 4, 2011 10:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 18,064, Visits: 16,099
I didn't like this question very much for the same reasons others have already noted. That said, I did learn a bit from it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1042559
Posted Tuesday, January 4, 2011 10:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:49 AM
Points: 2,620, Visits: 2,467
CirquedeSQLeil (1/4/2011)
I didn't like this question very much for the same reasons others have already noted. That said, I did learn a bit from it.

I agree, I learned to read more and more carefully the qotd else nothing new under the Sun.
Who has much time to spend for a qotd?
Post #1042570
Posted Tuesday, January 4, 2011 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 3, 2014 11:44 PM
Points: 39, Visits: 75
Good question. Really appreciating. It giving importance to Union , Truncate in Transaction.


The IN operator used in the query is very nice. Thanks.

Thank You.
Reji P R
Hyderabad


Thanks.

Reji PR,
Bangalore

Post #1042593
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse