What is the Difference Between Delete and Truncate?

🆚 Go to Comparative Table 🆚

The main difference between the DELETE and TRUNCATE commands in SQL lies in their purpose, speed, and transaction logging. Here are the key differences between the two:

  • Purpose: DELETE is used to remove specific rows from a table, while TRUNCATE is used to remove all rows from a table without resetting the table's identity.
  • Speed: TRUNCATE is generally faster than DELETE because it deallocates data pages instead of rows, requiring fewer locks and resources. DELETE, on the other hand, acquires locks on each deleting record, thus requiring more locks and resources.
  • Transaction Logging: DELETE records an entry in the transaction log for each deleted row, while TRUNCATE only records the page deallocations in the transaction log. This makes TRUNCATE a non-logged operation, meaning that the deleted data cannot be recovered in case of a database crash.
  • Rollback: DELETE allows you to roll back the deleted data, but TRUNCATE does not. Once the data is deleted using TRUNCATE, it cannot be recovered.
  • Trigger: DELETE can activate triggers, while TRUNCATE cannot.
  • Usage with Indexed Views: DELETE can be used with indexed views, but TRUNCATE cannot.
  • Permissions: To use TRUNCATE on a table, you need at least ALTER permission on the table.

In summary, DELETE is used to remove specific rows from a table and records each deletion in the transaction log, while TRUNCATE is used to remove all rows from a table without resetting the table's identity and does not record each deletion in the transaction log.

Comparative Table: Delete vs Truncate

Here is a table highlighting the differences between the DELETE and TRUNCATE commands in SQL:

Feature DELETE TRUNCATE
Definition Removes one or more rows from a table based on specified conditions. Removes all rows from a table without considering any conditions.
Language Data Manipulation Language (DML). Data Definition Language (DDL).
Commit Requires a manual COMMIT after making changes. Automatically commits the changes.
Where Clause Can use a WHERE clause to filter records. Cannot use a WHERE clause.
Transaction Space Consumes more transaction space. Consumes less transaction space.
Identity Does not reset the table identity. Resets the table identity.
Speed Slower than TRUNCATE. Faster than DELETE.
Tracking Records the deletion of each row in the transaction log. Does not record the deletion of each row in the transaction log.

Remember that the DELETE command is used to remove specific records from a table, while the TRUNCATE command is used to remove all records from a table. The DELETE command can be rolled back, whereas the TRUNCATE command cannot be rolled back.