Difference Between DROP DELETE and TRUNCATE
In SQL DELETE, DROP & TRUNCATE commands are mainly used to remove data from the database but each command works differently.
1. DELETE:
– Purpose: To Remove rows from a table. Can rollback. Its DML statment.
– Ex: DELETE FROM table_name WHERE condition;
– Feature:
– Remove specific rows based on a condition.
– Triggers are activated.
– Slower, as it logs individual row deletions.
2. DROP:
– Purpose: Permanently delete a table or Remove an entire database object (table, view, index, etc.). Can’t rollback. Its DDL statment.
– Ex: DROP TABLE table_name;
– Feature:
– Completely deletes the table and its data.
– Cannot be rolled back (non-transactional).
– All related permissions, indexes, and constraints are removed.
– Very fast.
3. TRUNCATE:
– Purpose: Removes all rows from a table. Can’t rollback. It is a DDL statement.
– Ex: TRUNCATE TABLE table_name;
– Feature:
– Removes all rows without logging individual row deletions.
– Cannot be rolled back in some databases (e.g., SQL Server), but can be in others (e.g., PostgreSQL) if used within a transaction.
– Resets identity columns.
– Triggers are not activated.
– Faster than DELETE for large tables because it uses less log space.
| DELETE | DROP | TRUNCATE |
| Its DDL statement. | Its DDL statement. | Row by Row data Delete. Can use WHERE clause |
| Can rollback | Can’t rollback | Can’t rollback |
| Ex: DELETE FROM table_name WHERE condition; | Ex: DROP TABLE table_name; | Ex: TRUNCATE TABLE table_name; |
| Can use WHERE clause | Can’t use where clause | Can’t use where clause |
| Row by Row data Delete.Can use WHERE clause | All data and structure drop simultaneously | Only all data will drop, truncate operation drop and recreate the table. |
Understanding and Summary:
– Use DELETE when you need to remove specific rows we can use this command. Row by Row data Delete.Can use WHERE clause
– Use DROP when you want to remove the table permanently then we use Drop Command.All data and structure drop simultaneously. Can’t use where clause
– Use TRUNCATE when you need to quickly remove all rows from a table but keep the table structure intact.Only all data will drop, truncate operation drop and recreate the table. Can’t use a where clause.
Difference Between DROP DELETE and TRUNCATE Difference Between DROP DELETE and TRUNCATE
Also Read :
What is a Synchronization task in Informatica?
What is a Runtime environment in Informatica?
What is Check In Check Out in Informatica powercenter (Versioning)