Home > Differences > SQL Basics – Difference between TRUNCATE, DELETE and DROP?

SQL Basics – Difference between TRUNCATE, DELETE and DROP?


DELETE and TRUNCATE are two SQL commands used to remove records from a particular table. But they differ in how they execute and operate.
 

–> DELETE: (MSDN)

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 in locked, thus it requires more number of locks and database resources.

6. According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.

7. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table.


 

–> TRUNCATE: (MSDN)

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, thus 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.

8. TRUNCATE is not possible when a table:
a. is reference by a Foreign Key or tables used in replication or with Indexed views.
b. participates in an Indexed/Materialized View.
c. published by using Transactional/Merge replication.

9. This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.

Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED if provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.


 

–> DROP: (MSDN)

1. The DROP TABLE command removes one or more table(s) from the database.

2. All related Data, Indexes, Triggers, Constraints, and Permission specifications for the Table are dropped by this operation.

3. Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.

4. Cannot drop a table that is referenced by any Foreign Key constraint.

5. According to MS BOL, Large tables and indexes that use more than 128 extents are dropped in two separate phases: Logical and Physical. In the Logical phase, the existing allocation units used by the table are marked for de-allocation and locked until the transaction commits. In the physical phase, the IAM pages marked for de-allocation are physically dropped in batches.


  1. November 16, 2013 at 11:52 am

    in SQL rollback is possible in all the three statements if used under transaction… refer the below post for more details…
    http://knowitbasic.blogspot.com/2013/09/difference-between-delete-and-truncate.html

    • November 16, 2013 at 12:04 pm

      Yes @george, you are right. I’ve already mentioned this thing in Note section, please check.

      ~manoj

    • November 18, 2013 at 10:42 am

      Yes, George it’s exactly what I wanted to say. Thanks for the link provided.

  2. April 3, 2013 at 9:25 am

    Hi there would you mind sharing which blog platform you’re working with? I’m planning to start my own blog in the near future but I’m having a tough time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I’m
    looking for something completely unique. P.S Apologies for getting off-topic but
    I had to ask!

  3. May 12, 2012 at 1:38 pm

    Please always classify your post.

    • November 18, 2013 at 11:00 am

      Hi @Asif, my posts as always to SQL Server, and not Oracle/MySQL/etc.
      Sorry for the confusion.

      ~manoj

  4. May 12, 2012 at 1:34 pm

    the above explainatin is for SQL standered (oracle/mysql etc.) but sql server does follow it’s

    own standered TSQL

    in which roll back is posiible in all three cases i,e. on Delete,Truncate and Drop. But you need

    to set the transaction before doing operation with ” Begin Transaction ” statement.

  5. Hridayeshwar Rao
    April 15, 2012 at 9:17 am

    DELETE
    The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

    SQL> SELECT COUNT(*) FROM emp;

    COUNT(*)
    ———-
    14

    SQL> DELETE FROM emp WHERE job = ‘CLERK';

    4 rows deleted.

    SQL> COMMIT;

    Commit complete.

    SQL> SELECT COUNT(*) FROM emp;

    COUNT(*)
    ———-
    10
    TRUNCATE
    TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.

    SQL> TRUNCATE TABLE emp;

    Table truncated.

    SQL> SELECT COUNT(*) FROM emp;

    COUNT(*)
    ———-
    0
    DROP
    The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

    SQL> DROP TABLE emp;

    Table dropped.

    SQL> SELECT * FROM emp;
    SELECT * FROM emp
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

    DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
    From Oracle 10g a table can be “undropped”. Example:

    SQL> FLASHBACK TABLE emp TO BEFORE DROP;

    Flashback complete.
    PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

  6. November 11, 2010 at 1:29 pm

    Rollback: DELETE and TRUNCATE both can be rolled back, provided both are enclosed in a TRANSACTION block. Once the transaction is COMMITTED you won’t be able to Rollback both.

    • May 12, 2012 at 1:37 pm

      roll back is possible in all three case (delete,truncate and drop) in SQLserver.

      • May 12, 2012 at 2:00 pm

        Yes Asif, you are correct.

        Thanks for your comments.
        ~Manoj

  7. August 21, 2010 at 5:10 pm

    Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations. How do you think it can be rolled-back if it isn’t logged? Rollback is *purely* driven by what’s in the transaction log.The difference are in functionality like:1. We can use WHERE clause with delete but not with truncate.2. Truncate reset the identity but Delete not.3. Truncate does not fire the trigger (on delete event) but Delete does.4. Truncate can be used if table is referenced by other foreign key tables but Delete can be used.More about Truncate can be rollbacked:http://blog.sqlauthority.com/2010/03/04/sql-server-rollback-truncate-command-in-transaction/

  1. July 7, 2015 at 2:12 pm
  2. June 4, 2015 at 7:01 am
  3. July 29, 2011 at 9:52 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 456 other followers

%d bloggers like this: