Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

TRUNCATE TABLE and ROLLBACK TRAN Expand / Collapse
Author
Message
Posted Monday, February 6, 2012 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 6,002, Visits: 8,265
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1247114
Posted Monday, February 6, 2012 1:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Hugo Kornelis (2/6/2012)

And 34% of them are for "you can never rollback a truncate table statement"

Maybe they are Oracle refugees? http://www.sqlservercentral.com/articles/Raw+Materials/71108/
Post #1247115
Posted Monday, February 6, 2012 1:25 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Nice and important question. This issue is coming up time to time when discussing differences of delete and truncate operation.
Post #1247116
Posted Monday, February 6, 2012 1:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 3,959, Visits: 5,194
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? http://www.sqlservercentral.com/articles/Raw+Materials/71108/


A TRUNCATE can be rolled back in Oracle as well....


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1247117
Posted Monday, February 6, 2012 1:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 6,002, Visits: 8,265
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? 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."



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1247120
Posted Monday, February 6, 2012 1:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 3,959, Visits: 5,194
Hugo Kornelis (2/6/2012)
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? 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."


Wonder when this was changed?
Granted I haven't worked in Oracle itself since 7b...


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1247125
Posted Monday, February 6, 2012 1:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Post #1247127
Posted Monday, February 6, 2012 2:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:40 AM
Points: 1,635, Visits: 242
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?
Post #1247130
Posted Monday, February 6, 2012 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:15 PM
Points: 2,278, Visits: 3,793
Very good ?

Mohammed Moinudheen
Post #1247132
Posted Monday, February 6, 2012 2:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 3,085, Visits: 3,281
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
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
Post #1247159
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse