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 «««34567»»

Difference between Truncate and Delete Expand / Collapse
Author
Message
Posted Sunday, July 3, 2011 11:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 11:07 PM
Points: 1, Visits: 47
QUESTION

Is "TRUNCATE" and "ROLLBACK" reliable?

If TRUNCATE de-allocates the pages, which can be overwritten by subsequent operations, can the ROLLBACK of a TRUNCATE operation restore the original data?

Eg.
BEGIN TRANSACTION
..
TRUNCATE table1
..
insert to other tables, which may overwrite the pages
(meanwhile, other operations outside this transaction could also re-use the de-allocated pages)
....
ROLLBACK

Is table1 restored to its original state?
Post #1135715
Posted Monday, July 4, 2011 12:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:50 AM
Points: 140, Visits: 455
Dear Prashant Pandey,
Nice explanation of Difference, I learned definitely something new from this post.
Post #1135731
Posted Monday, July 4, 2011 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 4, 2011 10:25 PM
Points: 1, Visits: 5
Hi..


Your article is good.But just want to know briefly about deallocation of data pages..?




Post #1135871
Posted Monday, July 4, 2011 5:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:37 AM
Points: 904, Visits: 1,491
Hi.

Thank you for the long explanations in the article. Much appreciated. A lot of information in a single post.

Two notes I'd like to make to help improve the article:

1) You state that TRUNCATE doesn't do any structure or table modification at the begining of the article and then you later state that "... the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations".


2) Try to refrain from using "I think" in technical articles.

Overall, a great article. :)
Keep up the good work. This sort of information is fundamental.

Best regards,


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1135878
Posted Monday, July 4, 2011 8:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 6, 2014 5:01 AM
Points: 4, Visits: 213
Excellent read. very simple and Bite size..just how i like to read.

keep up the good work
Post #1136024
Posted Monday, July 4, 2011 10:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 11:01 AM
Points: 1, Visits: 48
Good article. Missing info on how statistics are affected in each case.
Post #1136088
Posted Monday, July 4, 2011 10:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 11:28 AM
Points: 41, Visits: 383
I know when I worked with Oracle, a truncate would perform an implicit commit on any trancaction the truncate statement was riding on.

That may come as a BIG suprise when put into production!
Post #1136111
Posted Tuesday, July 5, 2011 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 7, 2011 12:13 PM
Points: 3, Visits: 5
Okay, I admit this is a trivia question from a wordsmith, but how did it come to pass that they use the word "truncate" in SQL to mean "nuke all the records from the table"? It seems a misuse of the English word. Maybe the person who invented SQL wasn't a skilled writer. "Truncate" means "to cut short" or "to remove a portion". It doesn't mean "to remove all". It seems something like "purge" might have been a better choice of verb.

Too late now, huh?

Post #1136552
Posted Wednesday, July 6, 2011 11:29 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:59 PM
Points: 629, Visits: 145
Good article. I knew that Truncate is minimal logged operation but didn't knew that it logs deallocation of data pages.

I have one question though. I know Delete is row by row operations. What happens if we delete rows in bulk? How SQL Sever logs this operation? If we do bulk delete, is it possible to rollback if it is not wrapped in transaction?

Thanks..!
Post #1137511
Posted Thursday, July 7, 2011 8:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 271, Visits: 315
virat_108 (4/14/2011)
You will get better and easy explanation over here:
http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html


That blog post states that delete cannot be rolled back. This is inaccurate. Both truncate and delete can be rolled back if they are within a begin tran/commit/rollback statement.



Post #1138159
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse