August 20, 2016 at 11:30 am
Comments posted to this topic are about the item TRUNCATE and ROLLBACK
August 20, 2016 at 11:31 am
Easy ..!!
Thanks for the question.
August 22, 2016 at 12:58 am
A good one for beginners to cut their teeth on.
Thank you for the question.
August 22, 2016 at 1:51 am
And time to get coffee
August 22, 2016 at 4:09 am
+1
And thanks Sergey for good explanation.
August 22, 2016 at 5:31 am
Thanks for a simple one to start the week.
August 22, 2016 at 5:33 am
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.
August 22, 2016 at 5:51 am
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.
August 22, 2016 at 6:27 am
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. (:
August 22, 2016 at 7:16 am
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/
August 22, 2016 at 7:46 am
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
August 22, 2016 at 8:07 am
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/
August 24, 2016 at 1:46 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy