Which one is faster DELETE/TRUNCATE?

Discussion in 'SQL Server' started by premchandra, Oct 9, 2013.

  1. premchandra

    premchandra Banned

    Joined:
    Sep 28, 2013
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Which one is faster DELETE/TRUNCATE?
     
  2. KiMO

    KiMO New Member

    Joined:
    Aug 24, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    TRUNCATE is faster, as it does not actually "deletes" the data, but instead, it marks the extents as empty and free for use. On the other hand, DELETE is a "per row" operation, which actually empties the extents' contents, thus performing slower.
     
  3. Claire_Anderson

    Claire_Anderson Member

    Joined:
    Aug 9, 2016
    Messages:
    41
    Likes Received:
    6
    Trophy Points:
    8
    Gender:
    Male
    Location:
    UK
    Home Page:
    http://www.hire-php-developers.co.uk/
    The delete operation can be rolled back or undo after deleting something from the database. Delete is used to remove records form the data base and it is a DML command. To make the changes done by the delete statement permanent, we use commit and rollback transaction.

    DML stands for Data Manipulation Language. The DML statements are used for managing data in database. The changes made by the DML commands are not permanent, they can be rolled back.

    Truncate is a DDL command. DDL stands for Data Definition Language. DDL statements are used to define the database structure or schema.

    Truncate is used to remove the all the records of a table but it does not remove the data structure of the table. The changes made by the truncate command cannot be undo or rolled back. Truncate command is faster than delete command because it uses fewer system and transaction log resources. The truncate command is similar to delete but it does not use the WHERE clause.
     
    shabbir likes this.
  4. Ami Desai

    Ami Desai Member

    Joined:
    Jan 5, 2017
    Messages:
    42
    Likes Received:
    17
    Trophy Points:
    8
    Location:
    Ahmedabad
    Home Page:
    http://www.ifourtechnolab.com/
    Hi,

    TRUNCATE will be executed faster than a DELETE if you want to quickly delete all rows from a table.

    Thanks
     

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