What is the difference between Delete, Truncate and Drop commands?

The DELETE, TRUNCATE, and DROP commands are used in SQL to remove data or objects from a database. However, they have different purposes and behaviors:

DELETE Command:

• Purpose: The DELETE command is used to remove specific rows from a table based on a specified condition.
• Behavior: When you use DELETE, you can specify a condition in the WHERE clause to indicate which rows should be deleted. It is a DML (Data Manipulation Language) command and can be rolled back using a transaction rollback if needed.
• Performance Impact: DELETE is a bit slower than TRUNCATE since it logs individual row deletions and maintains integrity constraints and triggers.
• Example: Deleting employees with a salary less than 50000 from the "Employees" table.

DELETE FROM Employees WHERE Salary < 50000;

 TRUNCATE Command:

• Purpose: The TRUNCATE command is used to remove all rows from a table, effectively emptying the table.
• Behavior: TRUNCATE is also a DML command, but it works differently from DELETE. It removes all data from the table without logging individual row deletions, which makes it faster than DELETE. It cannot be used with a WHERE clause since it deletes all rows in the table. TRUNCATE is also not logged in the transaction log, so it cannot be rolled back.
• Performance Impact: TRUNCATE is faster than DELETE, especially for large tables, as it deallocates the data pages quickly without logging each deletion.
• Example: Truncating the "Employees" table to remove all rows.

TRUNCATE TABLE Employees;

DROP Command:

• Purpose: The DROP command is used to remove database objects such as tables, views, or indexes from the database.
• Behavior: When you use DROP, the entire object is removed from the database schema, including all the data and related metadata. This operation is not recoverable, and it cannot be rolled back.
• Example: Dropping the "Employees" table from the database.
  

DROP TABLE Employees;

In summary:

• Use DELETE when you want to remove specific rows from a table based on a condition.
• Use TRUNCATE when you want to remove all rows from a table quickly and efficiently.
• Use DROP when you want to remove an entire database object (table, view, index, etc.) permanently from the database.