Home > Differences > Difference between TRUNCATE, DELETE and DROP?

Difference between TRUNCATE, DELETE and DROP?


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

DELETE: (http://msdn.microsoft.com/en-us/library/ms189835.aspx)
- Removes some or all 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.
- Causes all DELETE triggers on the table to fire.
- It deallocated records row-by-row in transaction logs and thus is slower than TRUNCATE.
- According to MS BOL, if a table is a heap or no clustered index is defined than the row-pages emptied are not deallocated and remain allocated in the heap. Thus no other object can reuse this associated space. Thus to deallocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
- Thus it requires more locks and database resources.
- This is a DML command as its is just used to manipulate/modify the table data. It does not change the property of a table.

TRUNCATE: (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
- Removes all rows from a table.
- Does not require a WHERE clause, not allowed here.
- Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
- No triggers are fired on this operation because it does not log individual rows.
- It deallocates data pages instead of rows in transaction logs, thus is faster than DELETE.
- Thus it also requires less number of locks.
- TRUNCATE is not possible when a table is reference by a Foreign Key or tables used in replication or with Indexed views.
- This is a DDL command as its resets identity columns, deallocates 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 rolled back. But in reality TRUNCATE operation can be ROLLED BACKED. Thus DELETE & TRUNCATE both can be rollbacked 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: (http://msdn.microsoft.com/en-us/library/ms173790.aspx)
The DROP TABLE command removes one or more table(s) from the database.
- All related data, indexes, triggers, constraints, and permission specifications for those tables are dropped by them operation.
- Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.
- Cannot drop a table that is referenced by any Foreign Key constraint.
- 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 deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

About these ads
  1. August 21, 2010 at 5:10 pm | #1

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

    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 | #3

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

      • May 12, 2012 at 2:00 pm | #4

        Yes Asif, you are correct.

        Thanks for your comments.
        ~Manoj

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

    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.

  4. May 12, 2012 at 1:34 pm | #6

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

    Please always classify your post.

    • November 18, 2013 at 11:00 am | #8

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

      ~manoj

  6. April 3, 2013 at 9:25 am | #9

    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. Angelo
    July 31, 2013 at 10:31 am | #10

    Great post. I also found truncate vs delete article quite good.

  8. November 16, 2013 at 11:52 am | #11

    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 | #12

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

      ~manoj

    • November 18, 2013 at 10:42 am | #13

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

  1. July 29, 2011 at 9:52 am | #1

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 379 other followers

%d bloggers like this: