February 6, 2012 at 1:50 am
This was removed by the editor as SPAM
February 6, 2012 at 1:52 am
Hugo Kornelis (2/6/2012)
Stewart "Arturius" Campbell (2/6/2012)
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."
In addition, a TRUNCATE TABLE statement is considered as a DDL operation in Oracle. DDL operations cannot be committed or rolled back explicitly in Oracle. http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1001.htm
Data Definition Language (DDL) Statements
Oracle Database implicitly commits the current transaction before and after every DDL statement.
The DDL statements are:
ALTER
...
TRUNCATE
February 6, 2012 at 2:02 am
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?
February 6, 2012 at 2:11 am
Very good ?
M&M
February 6, 2012 at 2:47 am
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
February 6, 2012 at 3:17 am
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
February 6, 2012 at 3:24 am
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
February 6, 2012 at 3:29 am
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
February 6, 2012 at 5:24 am
great question!!!
Thanks!
February 6, 2012 at 5:25 am
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!
February 6, 2012 at 5:45 am
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
February 6, 2012 at 6:01 am
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
February 6, 2012 at 7:59 am
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:
February 6, 2012 at 8:15 am
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
February 6, 2012 at 8:36 am
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
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply