How do you differentiate between truncate and delete?

Discussion in 'Database' started by aspguy, May 16, 2008.

  1. aspguy

    aspguy New Member

    Joined:
    May 2, 2005
    Messages:
    58
    Likes Received:
    1
    Trophy Points:
    0
    How do you differentiate between truncate and delete?
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    DELETE is an operation on a per row basis.

    TRUNCATE deallocates data pages which 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.

    Also truncate is a DDL command operation.
     
  3. Gloria-123

    Gloria-123 New Member

    Joined:
    Jun 10, 2008
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    I even don't get the choice to use the truncate. So when do you use this operation?
     
  4. skp819

    skp819 New Member

    Joined:
    Dec 8, 2008
    Messages:
    89
    Likes Received:
    3
    Trophy Points:
    0
    The Main Difference Between DELETE & TRUNCATE Are :-
    [1] DELETE - is a DML Command & TRUNCATE - is a DDL Command
    [2] After DELETE - can rollback the Records & After TRUNATE - cannot rollback the records
    [3] In DELETE Command you can give the conditions in WHERE Clause & In TRUNCATE you cannot give conditions
    [4] After using DELETE Command The memory will be occupied till the user does not give ROLLBACK or COMMIT & After using TRUNCATE Command The memory realeased immediately
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice