Delete Vs Truncate difference on rollback

Question

Delete Vs Truncate difference on rollback

Viewing 12 posts - 16 through 26 (of 26 total)

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

sqlnaive SSCoach Points: 17437 More actions · Answer 1

L' Eomot/Gail, I am aware with the rest of the differences between DELETE & TRUNCATE. Only had the doubt over the logging difference which was strangely wide spread across the websites saying that transaction cannot be rolled back while delete can.

Now after this discussion, I'm pretty much clear about it. It's only like the way these both commands are processed. 🙂

ScottPletcher SSC Guru Points: 101103 More actions · Answer 2

In Oracle, a TRUNCATE indeed cannot be rolled back. [Oracle uses a fundamentally different way of handling table allocations that would make it essentially impossible for Oracle to rollback a TRUNCATE.]

Many people assume this "rule" carries over to SQL Server, but it does not.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlnaive SSCoach Points: 17437 More actions · Answer 3

ScottPletcher (8/27/2013)
In Oracle, a TRUNCATE indeed cannot be rolled back. [Oracle uses a fundamentally different way of handling table allocations that would make it essentially impossible for Oracle to rollback a TRUNCATE.]
Many people assume this "rule" carries over to SQL Server, but it does not.

Hah... Thats the catch. Thanks a lot Scott for pointing that out. 😛

I checked on few of the oracle forums and found the following link:

http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

It says:

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

sqlnaive SSCoach Points: 17437 More actions · Answer 4

L' Eomot/Gail/Jeff/Scott, One more thing to confirm without wasting much of the time. So it means under a transaction, once the truncate is fired against a table, the data pages will be disallocated from the table but they are not up for sale (for write) by any other process till the final decision is taken on that transaction, i.e. commit.

Is it possible to check which data pages were disallocated from truncate within the transaction ? And if yes, then without performing any commit/rollback, is it possible to try write something on those deallocated pages with the help of another process ? (May sound stupid)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sqlnaive (8/28/2013)
One more thing to confirm without wasting much of the time. So it means under a transaction, once the truncate is fired against a table, the data pages will be disallocated from the table but they are not up for sale (for write) by any other process till the final decision is taken on that transaction, i.e. commit.

They're marked for deallocation, locks and/or latches will protect those pages as they do every single data modification in SQL to ensure no one else can change the locked/latched structures

Is it possible to check which data pages were disallocated from truncate within the transaction ?

Possible I suppose. Will require digging into the internals a lot.

And if yes, then without performing any commit/rollback, is it possible to try write something on those deallocated pages with the help of another process ? (May sound stupid)

No.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlnaive SSCoach Points: 17437 More actions · Answer 6

Thanks a lot Gail. Now everything is crystal clear to me on this topic. 🙂

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

sqlnaive (8/28/2013)
L' Eomot/Gail/Jeff/Scott, One more thing to confirm without wasting much of the time. So it means under a transaction, once the truncate is fired against a table, the data pages will be disallocated from the table but they are not up for sale (for write) by any other process till the final decision is taken on that transaction, i.e. commit.
Is it possible to check which data pages were disallocated from truncate within the transaction ? And if yes, then without performing any commit/rollback, is it possible to try write something on those deallocated pages with the help of another process ? (May sound stupid)

Gail answered the first part of the above. On the second part, my question would be why would you want to know which pages were deallocated to begin with? And if you could find out, why would you want to overwrite those pages (not that you can... just curious why you asked).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnaive SSCoach Points: 17437 More actions · Answer 8

Jeff, I had doubt whether we can rollback the truncate only because those data pages deallocated becuase of truncate operation do not get overwrite immediately and hence the second & third question (both were related). However Gail's third answer cleared the doubt and no answer was required for second question. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Same reason you can roll back a delete - the space where the rows were cannot be reused until the transaction commits

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

neerajyadav02 Old Hand Points: 316 More actions · Answer 10

I found excellent article on difference between truncate and delete.

http://www.passionforsql.com/2014/01/what-is-difference-between-truncate-and.html

neerajyadav02 Old Hand Points: 316 More actions · Answer 11

I found excellent article on difference between truncate and delete.

http://www.passionforsql.com/2014/01/what-is-difference-between-truncate-and.html

RK Mandava SSCarpal Tunnel Points: 4153 More actions · Answer 12

excellent discussion and learned some new things 🙂