Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

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

Thanks,
Prashant
Martin Bastable
Martin Bastable
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

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

thanks!
Jwalant Natvarlal Soneji
Jwalant Natvarlal Soneji
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 77
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.

Jwalant Natvarlal Soneji
BE IT, India
http://JwalantSoneji.com


Stephen Wilson
Stephen Wilson
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

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

Group: General Forum Members
Points: 6 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 Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 59
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 (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 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
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 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
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 557
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
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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