1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    59
    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,293
    Likes Received:
    365
    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