The Difference between Truncate and Delete in SQL Server

  • Prashant Pandey

    SSChasing Mays

    Points: 657

    Comments posted to this topic are about the item The Difference between Truncate and Delete in SQL Server

    Thanks,
    Prashant

  • Martin Bastable

    SSCommitted

    Points: 1656

    I learnt something new today 🙂

    thanks!

  • Jwalant Natvarlal Soneji

    Mr or Mrs. 500

    Points: 505

    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.

  • Stephen Wilson

    SSC Veteran

    Points: 260

    I knew about Truncate, but you have provided useful information about how it differs from DELETE FROM. Thanks

  • Paul Grubb

    SSC Enthusiast

    Points: 124

    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

    SSC Enthusiast

    Points: 185

    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

    SSC Enthusiast

    Points: 140

    Realy this is a wonderful article :), it changed my worng ideas about the difference between delete and truncate.

  • Jai Kumar

    SSC Eights!

    Points: 872

    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

    Hall of Fame

    Points: 3596

    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

    SSChasing Mays

    Points: 657

    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

  • Prashant Pandey

    SSChasing Mays

    Points: 657

    Mark Fyffe (11/22/2007)


    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,

    Hi Mark,

    Its very good for me and for all readers if u'll share your knowledge with us.

    Please tell me about this,???

    Thank you.

    Thanks,
    Prashant

  • Jai Kumar

    SSC Eights!

    Points: 872

    Thanks for the clarification.

    We are expecting more articles like this from you.

  • mollari_uk

    Grasshopper

    Points: 15

    Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.

    Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).

  • Mark F-428640

    Hall of Fame

    Points: 3596

    Actually my mistake this does work on temp tables but it does not work on variable tables nor does the drop table command. i.e.

    declare @t1 table

    (

    c1 varchar(10)

    )

    insert into @t1 values('chess')

    select * from @t1

    truncate table @t1 -- this will give you an error but using the below commented delete statement

    --would work

    --delete @t1

    select * from @t1

    --Drop table statement below also would give you an error if run.

    --Drop table @t1

  • Prashant Pandey

    SSChasing Mays

    Points: 657

    mollari_uk (11/22/2007)


    Why do these two behave differently? I don't know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don't want to reset you counter, then delete is there for you.

    Just thought I'd chip in with my explaination for this: With delete you aren't always removing all of the rows from the table so you wouldn't want it to reset the counter. Truncate does it the other way because you are always deleting all of the rows (and it gives you the option in contrast to delete).

    Hey mollari, You are right, delete do not resets the counter caz we can also perform the conditional based deletion with delete, But with Truncate we can't.

    Thank you,:)

    Thanks,
    Prashant

Viewing 15 posts - 1 through 15 (of 67 total)

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