Difference between TRUNCATE table and DELETE table

 TRUNCATE TABLE and DELETE are two SQL statements used to remove data from a table in a database. However, they differ in terms of their functionality, speed, and impact. Here's a breakdown of the differences between TRUNCATE TABLE and DELETE:

1. TRUNCATE TABLE:

• Functionality: TRUNCATE TABLE is used to remove all the data from a table while keeping the structure intact. It effectively resets the table, making it empty.
• Speed: TRUNCATE TABLE is generally faster than DELETE because it's a minimally logged operation. It removes data in large blocks and deallocates space, resulting in less overhead.
• Rollback: Unlike DELETE, TRUNCATE TABLE cannot be rolled back. Once data is truncated, it cannot be recovered using a rollback operation.
• Triggers: TRUNCATE TABLE does not activate delete triggers associated with the table.
• Restrictions: It's important to note that you cannot use TRUNCATE TABLE on a table that is referenced by a foreign key constraint or participates in an indexed view.
• In Memory: Truncate is a DDL statement. It also involves two commits, one before the statement execution and one after, meaning that after truncating a table, the data cannot be recovered. Due to this, truncate recovers space from memory.

Example:

sql
TRUNCATE TABLE employees;

2. DELETE:

• Functionality: DELETE is used to remove specific rows from a table based on a specified condition.
• Speed: Compared to TRUNCATE TABLE, DELETE can be slower, especially when deleting a large number of rows. Each row is logged individually, leading to more overhead.
• Rollback: A DELETE operation can be rolled back, allowing you to recover data if necessary.
• Triggers: DELETE activates delete triggers associated with the table, providing more control over the deletion process.
• Foreign Keys: When using DELETE, you need to handle foreign key constraints. Depending on how foreign key relationships are defined, you might need to delete related rows from other tables before deleting from the main table.
• In Memory: Delete is DML statement, merely data manipulation. After executing a delete statement, we can do a Rollback to get the data back, or do a commit. The delete does not recover memory.

Example:

sql
DELETE FROM employees WHERE department_id = 10;

In Summary:

• Use TRUNCATE TABLE when you want to quickly remove all data from a table and reset its structure. It's faster but less flexible compared to DELETE.
• Use DELETE when you want to remove specific rows from a table based on a condition. It provides more control, triggers activation, and allows rollback.

The choice between TRUNCATE TABLE and DELETE depends on your specific needs. If you need to quickly remove all data from a table, and you don't need the option to roll back, TRUNCATE TABLE is generally the better choice. If you need more control over the deletion process and want the option to roll back, use DELETE.