Post reply

Truncate and Delete

  • March 30, 2005 at 9:22 am

    #362304

    what is the difference between Truncate table and Delete from table ?

     


    subban

  • March 30, 2005 at 9:29 am

    #549581

    Delete is a logged operation. Which means it can be pretty easily rolledback if it's an error. A truncate is not logged, therefore faster but cannot be easily recovered.

  • March 30, 2005 at 9:36 am

    #549582

    More differences:

    Truncate cannot be used if there are foreign keys referencing the table.

    Truncate resets any Identity columns to their Seed value, Delete does not.

  • March 30, 2005 at 11:09 am

    #549600

    Actually, TRUNCATE is logged, and it can be rolled back.  DELETE operations log the deletion of each row, while TRUNCATE logs the dealocation of data pages.  This is a very common misunderstanding.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • March 31, 2005 at 1:08 am

    #549731

    Yes, TRUNCATE is minimally logged and as such described in BOL.

    Further constraints for TRUNCATE. I think you need to be db_owner, ddl_admin or owner of that table in order to run TRUNCATE.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 1:14 am

    #549733

    OK, enough inputs...... thanks a lot


    subban

  • March 31, 2005 at 4:18 am

    #549775

    Further clarification.

    Delete is a physical delete - each row gets deleted and logged.

    Truncate is a logical delete that deallocates the extents used by the table, marking them as empty for reuse, although the physical data pages still contain the data rows. The deallocation of the extents is logged.

  • March 31, 2005 at 4:21 am

    #549776

    Is it really a physical delete or isn't the record marked as ghost record?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 5:14 am

    #549781

    I don't know about 'ghost' records but I've had to deal with a few 'zombie' processes in my time

  • March 31, 2005 at 5:16 am

    #549782

    The zonbies in management are even worse

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 6:32 am

    #549791

    One more small difference...  You can grant an account delete permissions, but you cannot grant truncate. 

  • March 31, 2005 at 7:05 am

    #549806

    is there anything like

    Truncate will release the used space to OS and Delete will not do this

     


    subban

  • March 31, 2005 at 7:16 am

    #549808

    No, neither will be done automatically. SQL Server has a housekeeping thread that checks for such records, but I think they are not automatically release to the OS. Only when you shrink the file this happens.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 31, 2005 at 7:19 am

    #549810

    and why would you want to give anything back to the OS (greed thing that it is), might as well keep it for SQL to use

  • March 31, 2005 at 6:10 pm

    #549965

    It is truly amazing at how quick one of these threads can go down hill!!! 🙂

    I guess that is one of the reasons I keep reading them!!

    Darrell

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

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