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

SQL Basics – Difference between TRUNCATE, DELETE and DROP?

February 22, 2009 Leave a comment Go to comments

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


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.



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.



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. 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/

  2. 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.

    • Nishi kant
      July 25, 2017 at 10:20 pm

      can you explain the “provide both are enclosed in a TRANSACTION BLOCK” will be rollback.this statement is not clear for me.

      if we do commit for command delete and truncate , can we rollback?

      • July 25, 2017 at 10:44 pm

        Nishikant, after commit you won’t be able to rollback for both DELETE & TRUNCATE. But within explicit TRANSACTION (BEGIN TRAN, COMMIT TRAN) if you can rollback both of them.

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

    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.




    4 rows deleted.


    Commit complete.


    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.


    Table truncated.


    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:


    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.

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


  6. 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!

  7. 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…

    • November 16, 2013 at 12:04 pm

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


    • November 18, 2013 at 10:42 am

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

  8. sam
    March 5, 2016 at 3:14 pm

    Voted Yes, nicely written article but, TRUNCATE FROM table statement is no doubt much faster statement and easier than the DELETE Statement, but it’s not safer as like DELETE Statement.
    During a google search I found this helpful article here also the terms delete drop and truncate are defined very well. http://sqltechtips.blogspot.in/2015/10/truncate-delete-and-drop-in-sql.html

    • March 5, 2016 at 3:38 pm

      Sam, thanks for your comments.

      Read that blog with caution, I do not agree with some of its points, like:
      – Truncate statement mostly used to remove the data for always from the pre-stored table. (you can get back the data)
      – Truncate statement can’t be rolled back. (can be rolledback within a transaction)
      – TRUNCATE statement is unable logged consistently, it’s typically not logged. (is a logged operation, page wise logging).

      And in 6th point above I’ve mentioned TRUNCATE is faster than DELETE with reason.

  1. July 29, 2011 at 9:52 am
  2. June 4, 2015 at 7:01 am
  3. July 7, 2015 at 2:12 pm
  4. November 10, 2016 at 2:49 pm
  5. January 2, 2017 at 5:39 pm
  6. January 1, 2018 at 5:10 pm
  7. January 1, 2019 at 10:27 pm

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: