February 4, 2012 at 8:48 pm
Comments posted to this topic are about the item TRUNCATE TABLE and ROLLBACK TRAN
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 4, 2012 at 8:48 pm
February 4, 2012 at 8:58 pm
Good question; there are a number of links you could have included in the explanation, but one of my favourites is this by Kalen Delaney:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 5, 2012 at 7:02 am
Easy one, thanks to Paul Randals DBA Myth A Day. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 5, 2012 at 9:45 pm
Thanks for the question, but please explain why:
No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode
isn't also correct?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 5, 2012 at 11:25 pm
Explanation
there is no documentation on a non-existant concern
I disagree. Here is the link: http://msdn.microsoft.com/en-us/library/ms191544.aspx
Transact-SQL Statements Allowed in Transactions
You can use all Transact-SQL statements in an explicit transaction, except for the following statements:
ALTER DATABASEALTER FULLTEXT CATALOG
ALTER FULLTEXT INDEX
BACKUP
CREATE DATABASE
CREATE FULLTEXT CATALOG
CREATE FULLTEXT INDEX
DROP DATABASE
DROP FULLTEXT CATALOG
DROP FULLTEXT INDEX
RECONFIGURE
RESTORE
...
UPDATE STATISTICS can be used inside an explicit transaction. However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.
Based on this article, one can conclude that a TRUNCATE TABLE statement can be rolled back.
February 5, 2012 at 11:36 pm
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode
isn't also correct?
Because you can rollback a transaction with a TRUNCATE TABLE statement?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2012 at 12:20 am
Koen Verbeeck (2/5/2012)
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode
isn't also correct?
Because you can rollback a transaction with a TRUNCATE TABLE statement?
Tsk, thanks Koen....
Slow Monday morning for me....ignore me for the day ...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 6, 2012 at 12:40 am
Henrico Bekker (2/6/2012)
Koen Verbeeck (2/5/2012)
Henrico Bekker (2/5/2012)
Thanks for the question, but please explain why:No, you can never rollback a transaction with a TRUNCATE TABLE statement, regardless of recovery mode
isn't also correct?
Because you can rollback a transaction with a TRUNCATE TABLE statement?
Tsk, thanks Koen....
Slow Monday morning for me....ignore me for the day ...
Okidoki 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 6, 2012 at 12:52 am
This was removed by the editor as SPAM
February 6, 2012 at 1:13 am
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.
February 6, 2012 at 1:22 am
Hugo Kornelis (2/6/2012)
And 34% of them are for "you can never rollback a truncate table statement"
Maybe they are Oracle refugees? :hehe: http://www.sqlservercentral.com/articles/Raw+Materials/71108/
February 6, 2012 at 1:25 am
Nice and important question. This issue is coming up time to time when discussing differences of delete and truncate operation.
February 6, 2012 at 1:26 am
This was removed by the editor as SPAM
February 6, 2012 at 1:37 am
Stewart "Arturius" Campbell (2/6/2012)
vk-kirov (2/6/2012)
Hugo Kornelis (2/6/2012)
And 34% of them are for "you can never rollback a truncate table statement"Maybe they are Oracle refugees? :hehe: http://www.sqlservercentral.com/articles/Raw+Materials/71108/
A TRUNCATE can be rolled back in Oracle as well....
I've never used Oracle, so I can't speak from experience - but the official Oracle documentation disagrees with you:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm
"Caution:
You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated."
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply