Truncate rollback

  • Comments posted to this topic are about the item Truncate rollback

  • 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

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

    The Redneck DBA

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

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

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

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

    [/font]

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

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

  • 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

    😛

  • 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

  • Its Getting Error Or ?

  • 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

  • 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

  • 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

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

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