What is the difference between delete, drop and truncate?
DELETE DROP TRUNCATE
It removes Some or All rows from a table. It removes a table from the database. It removes all rows from a table.
A WHERE clause is used to remove particular rows according to the matched condition. All rows will be deleted when we did not use Where condition in Query. It does not require a WHERE clause. It does not require a WHERE clause
The DELETE command is Data Manipulation Language Command The DROP command is Data Definition Language Command. The TRUNCATE command is a Data Definition Language Command.
The DELETE command is more sluggish as compared to the DROP, and the TRUNCATE commands as it removes the rows according to the condition. The DROP Command offers better performance than the DELETE Command but not as in comparison to the Truncate Command since the DROP command erases all tables from databases immediately after the rows have been deleted. The TRUNCATE command performs better that the DROP or DELETE because it removes all records from the table with no condition.
The Integrity Constraints remain same for the DELETE command. The Integrity Constraints disappear with this DROP command. The Integrity Constraints are not removed from the TRUNCATE command.
DELETE operation can be rolled back. DROP operation cannot be rolled back. TRUNCATE operation cannot be rolled back.
DELETE FROM TableName WHERE condition; DROP TABLE table_name; TRUNCATE TABLE table_name;
BY Best Interview Question ON 12 Jan 2023