SQL INTERVIEW PREPARATION PART-13

Explain the difference between DELETE and TRUNCATE commands in SQL.

Answer:
- DELETE: This command is used to delete rows from a table based on a condition. It is a DML (Data Manipulation Language) operation. Each row deletion is logged individually, and you can use a WHERE clause to specify which rows to delete. It can be rolled back if used within a transaction.

DELETE FROM employees WHERE department_id = 10;

- TRUNCATE: This command is used to delete all rows from a table. It is a DDL (Data Definition Language) operation. It is faster than DELETE because it deallocates the data pages rather than logging individual row deletions. It cannot be rolled back in some databases because it does not log individual row deletions.

TRUNCATE TABLE employees;

Tip: Mention that DELETE can be more flexible when you need to remove specific rows, while TRUNCATE is more efficient for removing all rows. Also, point out that TRUNCATE resets any auto-increment counters on the table and may require higher privileges compared to DELETE.

Share with credits: https://t.me/sqlspecialist

Like this post if you want me to continue SQL Interview Preparation Series 👍❤️

Hope it helps :)