TRUNCATE in TRANSACTION

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

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

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

  • This was removed by the editor as SPAM

  • 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

  • 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

    Follow me on
    Twitter: @sqltwins

  • 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

  • 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

  • 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/

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

  • 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

  • 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

  • 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!

  • 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

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

  • 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 60 total)

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