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

Difference between Truncate and Delete Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2007 9:13 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, Visits: 128
Comments posted to this topic are about the item Difference between Truncate and Delete

Thanks,
Prashant
Post #424812
Posted Thursday, November 22, 2007 2:04 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:54 AM
Points: 171, Visits: 229
I learnt something new today :)

thanks!
Post #424875
Posted Thursday, November 22, 2007 2:29 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 7:59 AM
Points: 21, Visits: 74
I too. :)
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

Post #424886
Posted Thursday, November 22, 2007 2:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 9, 2012 1:09 AM
Points: 18, Visits: 44
I knew about Truncate, but you have provided useful information about how it differs from DELETE FROM. Thanks
Post #424888
Posted Thursday, November 22, 2007 2:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 4:36 AM
Points: 4, Visits: 65
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?
Post #424893
Posted Thursday, November 22, 2007 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 6, 2010 7:39 AM
Points: 75, Visits: 58
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.
Post #424901
Posted Thursday, November 22, 2007 3:25 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 5:40 AM
Points: 110, Visits: 77
Realy this is a wonderful article :), it changed my worng ideas about the difference between delete and truncate.
Post #424907
Posted Thursday, November 22, 2007 4:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, May 30, 2011 5:15 AM
Points: 782, 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?
Post #424923
Posted Thursday, November 22, 2007 5:03 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 23, 2014 12:32 PM
Points: 177, Visits: 550
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,
Post #424932
Posted Thursday, November 22, 2007 7:27 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2008 9:27 PM
Points: 81, 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.:)


Thanks,
Prashant
Post #424996
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse