Forum

What is the difference between DELETE and TRUNCATE in SQL?

This thread contains 1 reply, has 2 voices, and was last updated by  Nitesh User Do not delete 6 months, 3 weeks ago.

  • Author Replies
  • #52284

    What is the difference between DELETE and TRUNCATE in SQL?


  • #52285

    Hello,

    The difference between Delete and Truncate command is the most basic concept in SQL yet it confuses many people.

    DELETE:
    1. Removes Some or All rows from a table.

    2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.

    3. Causes all DELETE triggers on the table to fire.

    4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.

    5. Every deleted row is locked, thus it requires more number of locks and database resources.

    TRUNCATE:
    1. Removes All rows from a table.

    2. Does not require a WHERE clause, so you can not filter rows while Truncating.

    3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].

    4. IDENTITY columns are re-seeded on this operation if no seed was defined then the default value 1 is used.

    5. No Triggers are fired on this operation because it does not operate on individual rows.

    6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, this is faster than DELETE.

    7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.


    Regards,
    Nitesh Bavishiya

You must be to reply to this thread.Please or . Registration is 100% free.

GET FREE CONSULTATION

Call Us Now For Free Consultation97370 05566

Our experts listen to you patiently and suggest you the right course after conducting a personality profile test. Register your interest below to schedule personality profile test for you.

SUBSCRIBE TO OUR NEWSLETTER

Contact Us

Working Hours

  • Monday9:00 AM – 9:00 PM
  • Tuesday9:00 AM – 9:00 PM
  • Wednesday9:00 AM – 9:00 PM
  • Thursday9:00 AM – 9:00 PM
  • Friday9:00 AM – 9:00 PM
  • Saturday9:00 AM – 9:00 PM
  • SundayClosed
Copyright 2016-2019 Smart Mentors. All Rights Reserved.

Login

FORGOT PASSWORD

Sign up now to Become An Instructor

Register your Interest