Truncate rollback

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Comments posted to this topic are about the item Truncate rollback

  • tasneem 27027

    SSC Journeyman

    Points: 97

    use tempdb

    go

    create table a (b int)

    go

    insert into a (b) values (1), (2), (3)

    select * from a

    begin tran

    truncate table a

    select * from a

    rollback tran

    select * from a

    drop table a

    This would ideally give you an exception at the insert statement. Because as far I know the comma seperated value insertion as mentioned in the above insert statement is incorrect. So answer should be an exception/error

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Doh! I've missed three in a row now. Good one though!

    The Redneck DBA

  • Pulivarthi Sasidhar

    SSCertifiable

    Points: 6706

    hi,

    insert into a (b) values (1), (2), (3)

    The above works fine...

    The following statement gives error

    insert into a (b) values 1,2, 3

    please explain brief.....that what () means is :- as individual Insertion of each value or like Union all.....

  • Open Minded

    SSCommitted

    Points: 1842

    The newbie was partially right, when I pasted the statements, it erred at

    insert into a (b) values (1), (2), (3)

    I am using SQL Server 2005. I dug around the Internet, and saw it is supposed to work with MySql and Sql Server 2008?

    I got the answer right because the point was about rollback. Does everyone who answered right or wrong, will get half-points instead? :Whistling:

  • Abrar Ahmad_

    SSCarpal Tunnel

    Points: 4222

    [font="Verdana"]Goodish, easy hunt. but for the only SQL Server 2008 users.:-D

    It means its good to be updated. Cool ... :hehe:

    [/font]

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Good question. Not sure why it's worth 2 points; knowing that TRUNCATE can be rolled back should be elementary knowledge.

    Abrar Ahmad_ (4/22/2010)


    [font="Verdana"]Goodish, easy hunt. but for the only SQL Server 2008 users.:-D[/font]

    Why is it only for SQL 2008 users? You are not supposed to answer by running the code, but to guess based on your knowledge. If you follow announcements and read articles, you know about the new INSERT syntax in SQL2008. (But it would have been better if the words "SQL Server 2008" had been included somewhere in the question).


    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/

  • Kingston Dhasian

    SSCoach

    Points: 19794

    Nice question. Not many know that even a TRUNCATE can be rolled back.

    Some people as mentioned might get tricked by the INSERT statement and may choose the wrong answer.

    But on the other side, users who simply copy and paste the code in 2005 to know the answer will get what they deserve 0 points😀


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

    Yep !! It errored. But I'm being pedantic. Suggest you post psuedo code instead cos there are people out there who run this stuff and quite rightly the correct answer is ERROR.

    CodeOn

    😛

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hmmm, not really a good question, as the answer depends on what system you use.

    And no, knowing that it should work on 2008 is not an argument. In that case, it should explicitly say that the question is about 2008. If it is not mentioned, I assume it is for all systems (or at least 2000 and higher).

    I'm a 2005 user and unfortunately haven't got the change yet to work with 2008 (why don't those clients upgrade their systems, aargh), so I immediately choose Error.

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

  • Arjun SreeVastsva

    SSCertifiable

    Points: 7135

    Its Getting Error Or ?

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Depending on your settings, the error can also be a valid answer:

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'a'.

    Best Regards,

    Chris Büttner

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Christian Buettner-167247 (4/22/2010)


    Depending on your settings, the error can also be a valid answer:

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'a'.

    I'm curious, what settings give this behaviour?

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

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    The following setting will cause the error:

    SET IMPLICIT_TRANSACTIONS ON

    If this is turned on, the create table statement will also be rolled back, causing the error.

    Best Regards,

    Chris Büttner

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Hopla, learned something new today 🙂

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

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

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