The Difference between Truncate and Delete in SQL Server

  • You will get better and easy explanation over here:

  • You will get better and easy explanation over here:

  • Wonderful Article..... I rate it 100/100....

  • Hi Prasanth,

    Fantastic article. Hats off to you. It's really comprehensive and provides useful information. Thank you very much for writing such a good article.



  • This post really helps and give much more details. Thanks Prashant. Keep doing good job.


    Is "TRUNCATE" and "ROLLBACK" reliable?

    If TRUNCATE de-allocates the pages, which can be overwritten by subsequent operations, can the ROLLBACK of a TRUNCATE operation restore the original data?




    TRUNCATE table1


    insert to other tables, which may overwrite the pages

    (meanwhile, other operations outside this transaction could also re-use the de-allocated pages)



    Is table1 restored to its original state?

  • Dear Prashant Pandey,

    Nice explanation of Difference, I learned definitely something new from this post.

  • Hi..

    Your article is good.But just want to know briefly about deallocation of data pages..?

  • Hi.

    Thank you for the long explanations in the article. Much appreciated. A lot of information in a single post.

    Two notes I'd like to make to help improve the article:

    1) You state that TRUNCATE doesn't do any structure or table modification at the begining of the article and then you later state that "... the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations".

    2) Try to refrain from using "I think" in technical articles.

    Overall, a great article. 🙂

    Keep up the good work. This sort of information is fundamental.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst

  • Excellent read. very simple and Bite size..just how i like to read.

    keep up the good work 🙂

  • Good article. Missing info on how statistics are affected in each case.

  • I know when I worked with Oracle, a truncate would perform an implicit commit on any trancaction the truncate statement was riding on.

    That may come as a BIG suprise when put into production!

  • Okay, I admit this is a trivia question from a wordsmith, but how did it come to pass that they use the word "truncate" in SQL to mean "nuke all the records from the table"? It seems a misuse of the English word. Maybe the person who invented SQL wasn't a skilled writer. "Truncate" means "to cut short" or "to remove a portion". It doesn't mean "to remove all". It seems something like "purge" might have been a better choice of verb.

    Too late now, huh?


  • Good article. I knew that Truncate is minimal logged operation but didn't knew that it logs deallocation of data pages.

    I have one question though. I know Delete is row by row operations. What happens if we delete rows in bulk? How SQL Sever logs this operation? If we do bulk delete, is it possible to rollback if it is not wrapped in transaction?


  • virat_108 (4/14/2011)

    You will get better and easy explanation over here:

    That blog post states that delete cannot be rolled back. This is inaccurate. Both truncate and delete can be rolled back if they are within a begin tran/commit/rollback statement.

Viewing 15 posts - 46 through 60 (of 66 total)

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