Difference between Delete/Truncate/Drop in SQL.

In SQL, “DELETE,” “TRUNCATE,” and “DROP” are three different commands used to manipulate data and database objects. Each command serves a distinct purpose and has different effects:

1. **DELETE:**
   — Purpose: The DELETE command is used to remove rows from a table based on a specified condition.
   — Syntax:

DELETE FROM table_name WHERE condition;

— Notes:
— DELETE is a Data Manipulation Language (DML) command.
— It is used to selectively remove specific rows from a table based on the given condition.
— DELETE does not remove the table’s structure or definition, and it can be rolled back (if used within a transaction) to undo the changes.

2. **TRUNCATE:**
— Purpose: The TRUNCATE command is used to remove all rows from a table, effectively resetting the table to its original state.
— Syntax:

TRUNCATE TABLE table_name;

— Notes:
— TRUNCATE is a Data Definition Language (DDL) command.
— It is a faster and more efficient way to remove all rows from a table compared to DELETE, especially for large tables.
— Unlike DELETE, TRUNCATE does not log individual row deletions, making it non-rollbackable (cannot be undone with a rollback).
— TRUNCATE also resets the table’s auto-increment counters (if any) back to their starting values.

3. **DROP:**
— Purpose: The DROP command is used to remove database objects, such as tables, views, indexes, or entire databases.
— Syntax:

DROP TABLE table_name; - To drop a table
 DROP VIEW view_name; - To drop a view
 DROP INDEX index_name; - To drop an index
 DROP DATABASE database_name; - To drop a database

— Notes:
— DROP is a Data Definition Language (DDL) command.
— It is used to permanently remove a database object from the database.
— Once an object is dropped, it cannot be recovered, so it’s crucial to be cautious while using the DROP command.

In summary, DELETE is used to remove specific rows from a table based on a condition, TRUNCATE is used to remove all rows from a table, and DROP is used to permanently remove a database object, such as a table, view, index, or database itself. Each command has its own use case, and understanding the differences between them is essential for effective database management.