SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Truncate and Delete


Difference between Truncate and Delete

Author
Message
Prashant Pandey
Prashant Pandey
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 128
Comments posted to this topic are about the item Difference between Truncate and Delete

Thanks,
Prashant
Martin Bastable
Martin Bastable
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 248
I learnt something new today Smile

thanks!
Jwalant Soneji
Jwalant Soneji
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 78
I too. Smile
I like the details covered and I'm going to use trucate for all stagging tables used in ssis packages rather then deleting data from them.
Stephen Wilson
Stephen Wilson
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 44
I knew about Truncate, but you have provided useful information about how it differs from DELETE FROM. Thanks
Paul Grubb
Paul Grubb
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 87
I was wondering if you could clear up a issue someone once raised with me.
The chap told me one of the other differences between a Truncate and a Delete relates to the effect on full recovery models.
Is it true that using a truncate against a Full recovery model has a detrimental effect on restores in that the transaction logs are messed up?
Carl Janssen
Carl Janssen
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 62
Thanks for this wonderful article. I learned a lot. and you looked at the differences from many points of view.
I wanted to rate this article a 5 star. I did something wrong with my mouse and Awful was all I could select. I can't modify the rating any more. So the awful is from me and it should mean "awful good"! Thanks for taking your time to write such a good article.
Majdi Yassin-466113
Majdi Yassin-466113
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 91
Realy this is a wonderful article Smile, it changed my worng ideas about the difference between delete and truncate.
Jai Kumar
Jai Kumar
SSC Eights!
SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)SSC Eights! (802 reputation)

Group: General Forum Members
Points: 802 Visits: 41
Good one. Before reading this article I my idea was that there is no logging for TRUNCATE tables.

Thanks.

One question from my side

Is this article is applicable for both SQL 2000 and SQL 2005? Or only for SQL 2005?
Mark F-428640
Mark F-428640
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1358 Visits: 579
Excellent explanations especially the logging behaviour of both commands. I was hoping I would have saw some details on the behaviour of these commands on temporary tables. Feel free to let me/us know (via a forum post) if you enhance your article with this info as well.

Cheers,
Prashant Pandey
Prashant Pandey
Old Hand
Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)Old Hand (337 reputation)

Group: General Forum Members
Points: 337 Visits: 128
Jai Kumar (11/22/2007)
Good one. Before reading this article I my idea was that there is no logging for TRUNCATE tables.

Thanks.

One question from my side

Is this article is applicable for both SQL 2000 and SQL 2005? Or only for SQL 2005?


Hi Jai,
This article is applicable to both SQL 2000 and 2005 caz there is no difference between SQL 2000 and SQL 2005 Regarding these TRUNCATE and DELETE commands.Smile

Thanks,
Prashant
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search