SQL INTERVIEW PREPARATION PART-45

What are the differences between DELETE, TRUNCATE, and DROP commands in SQL? Provide examples.

Answer:

DELETE:
The DELETE command is used to remove rows from a table based on a specified condition. It can delete all rows or specific rows that match the condition. DELETE operations can be rolled back if they are part of a transaction.

Example:
DELETE FROM employees WHERE department_id = 10;
Key Points:
- Deletes specified rows.
- Can use a WHERE clause to filter rows.
- Triggers are fired.
- Slower compared to TRUNCATE for large data sets due to row-by-row deletion.
- Transactional and can be rolled back.

TRUNCATE:
The TRUNCATE command is used to remove all rows from a table. It is faster than DELETE because it does not log individual row deletions. TRUNCATE operations cannot be rolled back if they are not part of a transaction.

Example:
TRUNCATE TABLE employees;
Key Points:
- Deletes all rows from a table.
- Cannot use a WHERE clause.
- Resets table's identity column (if any).
- Does not fire triggers.
- Faster than DELETE due to minimal logging.
- Transactional and can be rolled back only if part of a transaction.