The difference in TRUNCATE and DELETE in Sql Server

I’ve answered this question many times, and answered it again this weekend.  What is the difference when doing a DELETE TableA instead of TRUNCATE TableA
A common misconception is that they do the same thing.  Not
so.  In fact, there are many differences between the two.

DELETE is a logged operation on a per row basis.  This means
that the deletion of each row gets logged and physically deleted.

You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

TRUNCATE is also a logged operation, but in a different way. 
TRUNCATE logs the deallocation of the data pages in which the data
exists.  The deallocation of data pages means that your data
rows still actually exist in the data pages, but the
extents have been marked as empty for reuse.  This is what
makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key
constraints.  You will have to remove the contraints, TRUNCATE the
table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed
value.  This means if you have a table with an identity column and
you have 264 rows with a seed value of 1, your last record will have
the value 264 (assuming you started with value 1) in its identity
columns.  After TRUNCATEing your table, when you insert a new
record into the empty table, the identity column will have a value of
1.  DELETE will not do this.  In the same scenario, if you
DELETEd your rows, when inserting a new row into the empty table, the
identity column will have a value of 265.

This entry was posted in Most Popular, Sql Development. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

64 Responses to The difference in TRUNCATE and DELETE in Sql Server

  1. kiran says:

    Excellent… very much userful and its exactly what iam looking for

  2. SQL says:

    ——————————————————————————–

    The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause

  3. pavan says:

    Nice n Informative post

    Thnx

  4. MeRcUrY says:

    Exactly What I was looking for THANKS! and Great post!

  5. Jansen says:

    Since the data rows still exist after truncate the size of the db doesn’t decrease after running truncate on many tables. Is it possible to release this data and reduce the size of the db?

  6. cherrie says:

    thank you this is very helpful all over the world =)

  7. Your tutorial is helpful for me.Thanks to YOU

  8. shahid says:

    Thanks

  9. hashim says:

    how i get back truncated table in sqlserver

  10. Piuesh says:

    Thanks

  11. Haresh says:

    what is the use of temporary table ?how we use it?

    how to pass dynamic parametyers in stored proc?

    while inserting data in a table through strored proc and the size of the field is

    less than any valued ,how we avoid this situation without changing the size of the field

    plz mail to hareshpurohitATtelstra.com

    thanks..

  12. Nice explanation; simple and concise.

  13. MIke says:

    Very helpful post. Short & to the point. Thanks!!

  14. sujan says:

    help ful information

  15. Hantus says:

    good explain…for better give some example

  16. Brijesh Nishad says:

    Wat a gud explation u have…!!!
    It’s amazing!!!
    really help full for us…

  17. Rajesh says:

    Given insight to the concepts. simply nice….

  18. vikram says:

    Hi, thanks for this article , its very easy to understand, i have question, if we use truncate the data will still present in the pages but they marked as empty for reuse,so what about the space,it wont release any space right?

  19. vikram says:

    thanks for the post ,can we delete a table that has constraints??

  20. Biaoge says:

    It seems that delete( usually follows a where clause) does not release table size even you delete a whole table.

  21. jimmy says:

    shows u do know what the shit is going on actually inside…. good one. thanks.

  22. Hardik says:

    This is very nice explanation ,,,

  23. Ruchi Kandwal says:

    it is good to understand.

  24. sathya says:

    The concepts is good. The other difference is Truncate will be available for the whole table. Delete can be done by row level.

  25. SRIKANTH says:

    Very nice and cool explanation.

  26. kate says:

    thanks a lot easy to understand.

  27. Suraj says:

    nice description

  28. sateesh says:

    Excellent

  29. sukhveer says:

    great job..

  30. karl says:

    Somewhat off topic, but…in a MySQL NDBCluster (in memory table-type), a DELETE won’t free up the memory used by the table – it’ll only make it available for subsequent inserts into it. That is, until the data nodes are re-started.

    A truncate will release memory used by a table without the need to restart a data node.

  31. Risham says:

    What about the table space concept, will it be freed in both the cases, delete n truncate???

  32. AK says:

    Great Post
    Helped me a lot

  33. Zubair Masoodi says:

    This Post made me to feel better to answer any one who will ask me the difference

  34. Sunil says:

    Hi it is really very good artical which is showing the difference between truncate and delete

  35. sandeep bhardwaj says:

    Thanks for clearing the concept, before geting this answer i thought that in truncate we can not roll back in any how.

    thanks again.

  36. sandeep bhardwaj says:

    Thanks for clearing the concept, before geting this answer i thought that we can not roll back in any how.

    thanks again.

  37. Maqsud says:

    Hi,
    very simple words, excellent post.

  38. Raj says:

    Excellent

  39. samir says:

    plz.
    inform me in my id
    thanks….

  40. samir says:

    sorry,
    i am unable to get my answers

  41. samir says:

    sorry,
    i am unable to retive my answers.

  42. samir says:

    what is the use of temporary table ?how we use it?
    how to pass dynamic parametyers in stored proc?

    while inserting data in a table through strored proc and the size of the field is

    less than any valued ,how we avoid this situation without changing the size of the field

    plz mail to

    thanks..

  43. samir says:

    while inserting data in a table through strored proc and the size of the field is

    less than any valued ,how we avoid this situation without changing the size of the field

    plz mail to

    thanks..

  44. samir says:

    while inserting data in a table through strored proc and the size of the field is
    less than any valued ,how we avoid this situation without changing the size of the field

    plz mail to

    thanks..

  45. Arun Mahendrakar says:

    Thanks for the info.. very simple words, very concise statements, very lucid explanation

  46. shaik afreen says:

    Thanks for this information
    which maks me to understand what exactly happens internally.

  47. Ramesh Galla says:

    Thanks…!

  48. Mohammad Iqbal says:

    Thanks for a nice answer !!

  49. san says:

    Its possible to use where condition in truncate table command

  50. Nitin Jagga says:

    Q: Is RollBack Possible in both case Delete And Truncate in Sql Server2000 ?

    Sol: Yes, in case of delete you can roll back, but in case of truncate, there’s a scenario where rollback is possible. Normally not, you can take truncate as delete + commit as truncate is a DDL Command.

    Scenario:

    BEGIN TRAN

    //DO

    TRUNCATE

    IF ERROR (OR ANY CONDITION)
    ROLLBACK

    COMMIT TRAN

    in this case, rollback will work even after truncate if it meets your condition.

    Other wise, truncate as i mentioned is a DDL Command and will work with auto commit status set as true.

    Regards

  51. raza ur rahman says:

    nice explaination

  52. raza ur rahman says:

    verry good

  53. Shakeeb Ahmed says:

    simple and nice explaination!!

  54. prasadrmarathe says:

    Thanks,
    But what abt RollBack
    Is RollBack Possible in both case Delete And Truncate in Sql Server2000

  55. Srikanth HN says:

    Thanks !! It helped ..
    Short but still easy to understand !

  56. hemant says:

    Thanks for a great info. It was asked two time in interviw and iwas having no answer
    thank for clearing the concept

  57. shishir says:

    Gud one!
    will help me for my interview :-)

  58. Rajiv Bhati says:

    hi I am Rajiv Bhati, i am a S/w Test professional(QA). the biggest problem that i face while performing testing,is this one i.e basic difference between delete table and truncate table. from your material i got some more information.
    thanks & regards
    Rajiv Bhati rajivbhati12@gmail.com

  59. Alan says:

    excellent post !!!

  60. Armando Prato says:

    One other difference to note… delete triggers are not fired when using TRUNCATE.

    Excellent post, btw

  61. Greg Low says:

    And the other big difference is: permissions.

    The fact that a user can DELETE rows does not mean they can TRUNCATE the table. By default, they need at least DDL_ADMIN to do that.

  62. Thomas says:

    nice explanation, I was asked this in an interview and couldn’t really explain the difference. thanx….

  63. btompkins says:

    Add this to the pile of stuff I didn’t know before I met you. Thanks!

  64. sahilmalik says:

    Simple concept + profound implications = good post.

Leave a Reply