TRUNCATE in TRANSACTION

  • Gobikannan

    SSCrazy

    Points: 2735

    Comments posted to this topic are about the item TRUNCATE in TRANSACTION

    -----------------
    Gobikannan

  • Revenant

    SSC-Forever

    Points: 42467

    Gee, lots of code but actually very simple -- thanks!

  • This was removed by the editor as SPAM

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Good question, but with lots of trickery.

    What point were you trying to make? Duplicates with UNION, TRUNCATE in transaction or columns referenced directly in the IN clause?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Nakul Vachhrajani

    SSChampion

    Points: 10156

    Good question.

    That being said, I fail to understand what the purpose of the question was - to test whether or not the user notices the "UNION", or that TRUNCATE can actually participate in a transaction.

    The fact that TRUNCATE can participate in a transaction is something that not many people know when asked to list down the differences between a DELETE and a TRUNCATE.

    By mixing up the "UNION" test and the TRUNCATE test, the question does draw the attention of the reader away from the important concept.

    All in all, good effort, but most people will continue to have the misconception that TRUNCATEs cannot participate in a transaction even after reading and answering correctly to this question.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • tommyh

    SSCertifiable

    Points: 6252

    Koen (da-zero) (1/4/2011)


    Bad question, with lots of trickery.

    What point were you trying to make? Duplicates with UNION, TRUNCATE in transaction or columns referenced directly in the IN clause?

    There fixed it for you. Or well... to more suite my opinion 😀

    /T

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Oh yeah, a tip for the author:

    if you really want to deceive people, you should've included the number of rows that had a 3 in the answer. Eight rows I think it was. I was looking for the answer with 8 rows, but since it wasn't there, it got me thinking...

    🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Hugo Kornelis

    SSC Guru

    Points: 64645

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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • cengland0

    SSCertifiable

    Points: 6102

    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.

  • Hardy21

    SSCrazy Eights

    Points: 9708

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

    Thanks

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    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

    SSChampion

    Points: 11139

    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

    SSCertifiable

    Points: 6102

    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

    SSC Guru

    Points: 294069

    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.

Viewing 15 posts - 1 through 15 (of 61 total)

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