locked
difference between delete and truncate in space of data and logged RRS feed

  • Question

  • I read more time between truncate and delete

    both of them remove data from table

    but until now i cannot know the different between both of them as following

    why truncate free the space of database but delete not

    what is meaning delete is logged operation and truncate also minimum logged

    Friday, October 21, 2016 3:44 PM

Answers

  • also i get that definition from Microsoft book 

    Database Fundamentals,
    Exam 98-364

    • TRUNCATE: Removes rows from a table and frees the space used by those rows.
    • DELETE: Remove rows from a table but does not free the space used by those rows
    removed.

    That is not entirely true I guess for delete the book means

    When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database

    There are many such cases


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, October 21, 2016 7:17 PM
    Answerer

All replies

  • Hello,

    TRUNCATE is more DDL command and equals a drop & re-create table, only the pointer to the first data page is removed; the operation is not logged.

    A DELETE is a DML command and fully logged. But both releases the space of the deleted data; why do you think it don't?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, October 21, 2016 3:49 PM
    Answerer

  • why truncate free the space of database but delete not

    what is meaning delete is logged operation and truncate also minimum logged

    Both would free space truncate normally truncates whole table and so frees lot of space while delete is mostly used with where cause so limited space would be utilized. I have explained bit more in below article

    http://social.technet.microsoft.com/wiki/contents/articles/26782.sql-server-an-examination-of-logging-in-truncate-table-statement-and-its-comparison-with-delete-statement.aspx


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by AV111 Friday, October 21, 2016 5:16 PM
    Friday, October 21, 2016 4:44 PM
    Answerer
  • http://www.codeproject.com/Questions/370759/Difference-between-delete-and-truncate

    Please click Mark As Answer if my post helped.

    Friday, October 21, 2016 5:16 PM
  • WHY truncate DDL command not DML

    DDL treat with structure not data

    but truncate treat with data not structure

    Friday, October 21, 2016 6:07 PM
  • also i get that definition from Microsoft book 

    Database Fundamentals,
    Exam 98-364

    • TRUNCATE: Removes rows from a table and frees the space used by those rows.
    • DELETE: Remove rows from a table but does not free the space used by those rows
    removed.

    Friday, October 21, 2016 6:27 PM
  • also i get that definition from Microsoft book 

    Database Fundamentals,
    Exam 98-364

    • TRUNCATE: Removes rows from a table and frees the space used by those rows.
    • DELETE: Remove rows from a table but does not free the space used by those rows
    removed.

    That is not entirely true I guess for delete the book means

    When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database

    There are many such cases


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, October 21, 2016 7:17 PM
    Answerer