Written by Sai Sandeep Thota on March 15, 2014 in SQL

Difference between Delete and Truncate Commands

GET ALERTS:

Get our Latest updates delivered to your mailbox!

Delete

Delete is used to remove the records that are contained in a Database Table. For example, if we have a table named Employees and we want to delete all the records from that table, then our SQL query looks like:

sql> DELETE * FROM employees;
or
sql> DELETE FROM employees;

If we want to delete only a specific record, say employee with employee id 1442 then we modify the above query accordingly.

sql> DELETE * FROM employees WHERE empid = 1442;

Difference between Delete and Truncate

Truncate

Truncate command is used to delete all* the records from a Table. Considering the same instance here, if we want to Delete all rows then we can use Truncate which is much faster than the Delete command.

sql> TRUNCATE TABLE employees;

Difference between DELETE and TRUNCATE Commands

You might think both are doing the same job by deleting the records in table, but they are different. Some differences are listed below:

  1. DELETE is a Data Manipulation Language (DML) command.
  2. TRUNCATE is a Data Definition Language (DDL) command.
  3. DELETE can be Rolled back to a COMMIT point, i.e., you can undo the changes which you have made.
  4. TRUNCATE cannot be Rolled back to a COMMIT point, i.e., tou cannot undo the changes which you have made.
  5. Using DELETE you can delete a specific record in a Table.
  6. Whereas using TRUNCATE you can delete All Records* of a Table.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: