TRUNCATE and ROLLBACK

Question

Post reply

TRUNCATE and ROLLBACK

sergey.gigoyan

Ten Centuries

Points: 1213
More actions
August 20, 2016 at 11:30 am

#329237

Comments posted to this topic are about the item TRUNCATE and ROLLBACK
August 22, 2016 at 1:06 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

August 20, 2016 at 11:31 am

#1896495

Easy ..!!

Thanks for the question.

DaveJenkins Mr or Mrs. 500 Points: 568 More actions · Answer 2

A good one for beginners to cut their teeth on.

Thank you for the question.

Rune Bivrin SSCrazy Eights Points: 8525 More actions · Answer 3

And time to get coffee 🙁

Just because you're right doesn't mean everybody else is wrong.

George Vobr SSChampion Points: 10009 More actions · Answer 4

George Vobr

SSChampion

Points: 10009

August 22, 2016 at 4:09 am

#1896581

+1

And thanks Sergey for good explanation. 🙂

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Thanks for a simple one to start the week.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 6

For those who thought this was easy: I previously worked with Oracle databases where TRUNCATE cannot be rolled back. I got this one right because I had previously been surprised by this difference in behavior.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

tom.w.brannon (8/22/2016)
For those who thought this was easy: I previously worked with Oracle databases where TRUNCATE cannot be rolled back. I got this one right because I had previously been surprised by this difference in behavior.

People sometimes forget that TRUNCATE is considered DDL, not DML. Oracle does an auto-commit of everything pending when you fire any DDL, then fires the DDL. Most people remember this after it happens once. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kaye Cahs SSCarpal Tunnel Points: 4135 More actions · Answer 8

Most people remember this after it happens once. 😛

I'm not like most people.

It's been over 15 years since I've worked in an Oracle environment and I still forget. (:

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Great back to basics question. We have seen this topic on QOTD many times. Still quite shocking that at the time of my comment there are 33% of the answers thinking that TRUNCATE will not be rolled back.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TomThomson SSC Guru Points: 104773 More actions · Answer 10

Sean Lange (8/22/2016)
Great back to basics question. We have seen this topic on QOTD many times. Still quite shocking that at the time of my comment there are 33% of the answers thinking that TRUNCATE will not be rolled back.

I agree that's shocking, but I'm even more shocked that 8% of answers believe that DELETE will not be rolled back.

Of course it's interesting that no DML trigger for TRUNCATE TABLE is supported because, although it's a DML statement, truncation is not in any DML Event Group because it isn't a DML Event. Seems a bit strange to me that the effect of a DML statement can somehow not be a DML event.

Tom

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

TomThomson (8/22/2016)
Sean Lange (8/22/2016)
Great back to basics question. We have seen this topic on QOTD many times. Still quite shocking that at the time of my comment there are 33% of the answers thinking that TRUNCATE will not be rolled back.
I agree that's shocking, but I'm even more shocked that 8% of answers believe that DELETE will not be rolled back.
Of course it's interesting that no DML trigger for TRUNCATE TABLE is supported because, although it's a DML statement, truncation is not in any DML Event Group because it isn't a DML Event. Seems a bit strange to me that the effect of a DML statement can somehow not be a DML event.

But TRUNCATE is not a DML statement. It is a DDL statement. While I have always thought it bizarre that it isn't a DML statement but it is well documented that isn't. You can capture truncate using an AUDIT in 2008+.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 12

Easy one, thanks.

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