TRUNCATE TABLE and ROLLBACK TRAN

Question

Post reply

TRUNCATE TABLE and ROLLBACK TRAN

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

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

M&M

SSC-Insane

Points: 21700

February 6, 2012 at 2:11 am

#1442941

Very good ?

M&M

Stuart Davies SSCoach Points: 19505 More actions · Answer 2

Hugo Kornelis (2/6/2012)
I wanted to comment that this question is superfluous, that everyone knows this. But.....
At the time of writing:
"Incorrect answers: 56% (52)"
And 34% of them are for "you can never rollback a truncate table statement"
.
.
.
.
.
.
Picks up jaw from floor.

It's got better -

Correct answers:42%(76)

Incorrect answers:58%(107)

Total attempts: 183

The myth still abounds regrettably

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The distribution of answers is frankly terrifying.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 4

ako58 (2/6/2012)
CREATE TABLE t(id INT IDENTITY, i INT)
BEGIN TRAN
INSERT INTO t VALUES (4),(2),(3)
SELECT * FROM t
ROLLBACK TRAN
SELECT * FROM t
INSERT INTO t VALUES (4),(2),(3)
SELECT * FROM t
where is rollback?

I don't get your question.

There is no TRUNCATE TABLE in your query and the first INSERT is complety rolled back, since the final result has only 3 rows.

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 6

great question!!!

Thanks!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 7

GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

I agree!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Good question, one that needs repeating (peferably with some variation) quite frequently since the pernicious myth that trucate table is not logged so can't be rolled back is still widespread, despite many valiant attempts to debunk it.

I was quite appalled by the number of people who appear to think this depends on the recovery model, or on bulk logging option settings. I think Gail's idea (above) of some questions to bolster people's knowledge of what recovery models are and do is an excellent one.

Tom

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 9

GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.

+1

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

Dana Medley SSCertifiable Points: 6764 More actions · Answer 10

Koen Verbeeck (2/6/2012)
GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.
+1

+ 2 :hehe:

Everything is awesome!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Is it bad form, if you create a QotD, to list your own article as a reference?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Stuart Davies SSCoach Points: 19505 More actions · Answer 12

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

If I were you, I would not give that a second thought

edited for missing "not" - sorry for any bad inference :blush:

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 13

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

If it's a good article? No, not at all. I've done it in the past.

That being said, I do always prefer an official MS reference. But if there isn't one, a third-party reference that is solid enough (read: based on other MS material, or equipped with enough code samples to prove that the statements are true) will do just fine.

I don't know your article, but I do know you - and that's enough for me to assume that your article will meet the bar. Easily!

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/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 14

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

Might not be bad form, but I can just image the list of those who whine and cry "give me my points".

So then ask some one else to be the author of the QOD or QODs who then can site your article(s) as justification.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 15

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

It's probably better to find another one just so there's input from two different people but if it's the best reference (that can be found without a quick search) I would say go for it. No sense in wasting time when you have a good, accurate article that you can find quickly.

TRUNCATE TABLE and ROLLBACK TRAN

Cookies on SQLServerCentral