Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRUNCATE in TRANSACTION


TRUNCATE in TRANSACTION

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 996
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. :-D

ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 Visits: 3575
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!
sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3354 Visits: 2848
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
cengland0
cengland0
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1300
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
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.
Cliff Jones
Cliff Jones
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4069 Visits: 3648
I fell into the same trap. I overlooked the WHERE clause. Not sure I understand what the point of that was?
phil.wood 94423
phil.wood 94423
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
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 Sad
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!
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21115 Visits: 18259
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

Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3627 Visits: 3236
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?
:-P
Reji PR
Reji PR
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 76
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
:-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search