Truncate rollback

Question

Post reply

Truncate rollback

Ninja's_RGR'us

SSC Guru

Points: 294069
More actions
April 21, 2010 at 9:20 pm

#221741

Comments posted to this topic are about the item Truncate rollback

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

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

tasneem 27027 SSC Journeyman Points: 97 More actions · Answer 1

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 More actions · Answer 2

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

The Redneck DBA

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 3

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: 1872 More actions · Answer 4

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: 4244 More actions · Answer 5

[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: 64790 More actions · Answer 6

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: 19799 More actions · Answer 7

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: 2382 More actions · Answer 8

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: 259115 More actions · Answer 9

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: 7141 More actions · Answer 10

Arjun SreeVastsva

SSCertifiable

Points: 7141

April 22, 2010 at 12:55 am

#1155880

Its Getting Error Or ?

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

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: 259115 More actions · Answer 12

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 More actions · Answer 13

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: 259115 More actions · Answer 14

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