SQL Interview Questions

In SQL, DELETE, TRUNCATE, and DROP are commands used to remove data or database objects. They serve different purposes and have different behaviors and implications.

DELETE

The DELETE statement is used to remove specific rows from a table based on a condition. It is part of the Data Manipulation Language (DML).

• Syntax:

  DELETE FROM table_name WHERE condition;

• Example:

  DELETE FROM employees WHERE employee_id = 101;

• Characteristics:

  • Row-by-Row Deletion: Deletes rows one at a time based on the condition.
  • Can Use WHERE Clause: Allows selective deletion of rows.
  • Transaction Safe: Can be rolled back if used within a transaction.
  • Triggers: Can activate triggers defined on the table.
  • Performance: Slower for large tables compared to TRUNCATE.

TRUNCATE

The TRUNCATE statement is used to remove all rows from a table, effectively resetting the table to its empty state. It is part of the Data Definition Language (DDL).

• Syntax:

  TRUNCATE TABLE table_name;

• Example:

  TRUNCATE TABLE employees;

• Characteristics:

  • Removes All Rows: Deletes all rows in the table without logging individual row deletions.
  • No WHERE Clause: Cannot be used to delete specific rows.
  • Faster: Generally faster than DELETE for large tables because it uses fewer system and transaction log resources.
  • Resets Identity Column: Resets any auto-increment counters on the table.
  • Transaction Safe: Can be rolled back if used within a transaction in some databases (e.g., SQL Server).
  • Triggers: Does not activate DELETE triggers, but some databases have TRUNCATE triggers.

DROP

The DROP statement is used to remove entire database objects, such as tables, views, indexes, or databases. It is part of the Data Definition Language (DDL).

• Syntax:

  DROP TABLE table_name;

• Example:

  DROP TABLE employees;

• Characteristics:

  • Removes Entire Object: Completely removes the table and its data, as well as any associated indexes, constraints, and triggers.
  • Irrecoverable: Cannot be rolled back once executed, except within a transaction in some databases.
  • Removes Dependencies: May fail if there are dependencies, such as foreign key constraints.
  • DDL Command: Affects the schema of the database.

Summary Table

Feature	DELETE	TRUNCATE	DROP
Operation	Deletes specific rows	Removes all rows	Removes entire table
Part of	DML	DDL	DDL
WHERE Clause	Yes	No	No
Logging	Logs individual row deletions	Minimal logging	No logging
Speed	Slower for large tables	Faster for large tables	Fast
Identity Column Reset	No	Yes	Not applicable
Triggers	Activates DELETE triggers	Does not activate DELETE triggers	Not applicable
Transaction Safe	Yes	Depends on DBMS	Depends on DBMS
Schema Changes	No	No	Yes
Use Case	Selective row deletion	Quickly remove all rows	Remove table structure

Conclusion

Understanding the differences between DELETE, TRUNCATE, and DROP is crucial for database management. Use DELETE for selective row removal, TRUNCATE for quickly clearing all rows from a table while keeping its structure, and DROP for completely removing database objects. Each command serves a different purpose and has its own implications, so choose the appropriate one based on your requirements.