TRUNCATE in TRANSACTION

  • I fell into the same trap. I overlooked the WHERE clause. Not sure I understand what the point of that was?

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

  • 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[/url]
    Learn Extended Events

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

    😛

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

  • I liked the question. I don't mind 'distractions' in the QOTD because I think that mirrors real life.

    But, I would have loved to see this as three different questions: 1) duplicates with UNION, 2) TRUNCATE in transaction, and 3) columns referenced in an IN clause (something I had never seen before.)

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

    Same thing for me, got the TRUNCATE and the IN worked out, and didn't pay enough attention to the UNION portion. From the results, and comments, most people appear to have done the same thing. At least I'm not alone. 😉

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

    Me too. I noticed the UNION (no ALL) but as the thing was labelled "TRUNCATE in TRANSACTION" I didn't look for duplicate rows.

    Tom

  • Good question but i think it will not come under Header Truncate in Transaction

    I think this question will come under UNION and UNION ALL diff.

  • amit_adarsh (1/4/2011)


    Good question but i think it will not come under Header Truncate in Transaction

    I think this question will come under UNION and UNION ALL diff.

    I think one title will always be insufficient for this question! The question attempts to test 3 different things:

    1. UNION v/s UNION ALL

    2. Use of TRUNCATE in a transaction

    3. Reader's understanding of the WHERE clause (which ranks #1 as a distraction factor for this question)

    This question ideally should have been 3 different questions.

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

    Follow me on
    Twitter: @sqltwins

  • Nakul Vachhrajani (1/4/2011)


    I think one title will always be insufficient for this question! The question attempts to test 3 different things:

    1. UNION v/s UNION ALL

    2. Use of TRUNCATE in a transaction

    3. Reader's understanding of the WHERE clause (which ranks #1 as a distraction factor for this question)

    This question ideally should have been 3 different questions.

    The thing is that the most important factor to solve the question correctly was not the TRUNCATE in the transaction, but the duplicates with the UNION. It's ok to have a distractor in your question, but you had a few too many, including the title.

    edit: but don't let this get in the way to create new questions 🙂

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

  • Fantastic question ... but just 1 point for this? I am a bit surprised 🙂

    As some of you have noted - I missed the Union All too. But where I got stumped was the IN Clause having column names. I swear I had not seen this before and this led me to believe the query is going to error out. I was cool with the Truncate participating in a transaction. I knew that once rolled back the data will be restored to the original state. But the IN clause did me in!

    Overall - very craftily created question and something which tested agility and sharpness.

    Thanks all...

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • Speaking of craftiness I just realized that the title of the question is TRUNCATE IN TRANSACTION and if you notice - the question tests every word of it --- TRUNACATE, IN, TRANSACTIONS...with one omission - UNION ALL 😛

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • wware

    I liked the question. I don't mind 'distractions' in the QOTD because I think that mirrors real life.

    Exactly. I would rather get it wrong on a QOTD and miss a point, than to get it wrong on a production system. Hmmm - maybe developers are tricky;-)

    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

  • Columns in the IN clause! 22 years of sql and that was a first. You're never too old to learn. Thanks!

Viewing 15 posts - 16 through 30 (of 61 total)

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