Difference between DROP and TRUNCATE in SQL

Prerequisite – DROP, and TRUNCATE in SQL

1. DROP :
DROP is a DDL(Data Definition Language) command and is used to remove table definition and indexes, data, constraints, triggers etc for that table. Performance-wise the DROP command is quick to perform but slower than TRUNCATE because it gives rise to complications. Unlike DELETE we can’t rollback the data after using the DROP command. In the DROP command, table space is freed from memory because it permanently delete table as well as all its contents.

Syntax of DROP command –

DROP TABLE table_name;

2. TRUNCATE :
TRUNCATE is a DDL(Data Definition Language) command. It is used to delete all the tuples from the table. Like the DROP command, the TRUNCATE command also does not contain a WHERE clause. The TRUNCATE command is faster than both the DROP and the DELETE command. Like the DROP command we also can’t rollback the data after using the this command.

Syntax of TRUNCATE command –

TRUNCATE TABLE table_name; 

Let’s see the difference between DROP and TRUNCATE command in SQL:-

S.NO	DROP	TRUNCATE
1.	The DROP command is used to remove table definition and its contents.	Whereas the TRUNCATE command is used to delete all the rows from the table.
2.	In the DROP command, table space is freed from memory.	While the TRUNCATE command does not free the table space from memory.
3.	DROP is a DDL(Data Definition Language) command.	Whereas the TRUNCATE is also a DDL(Data Definition Language) command.
4.	In the DROP command, view of table does not exist.	While in this command, view of table exist.
5.	In the DROP command, integrity constraints will be removed.	While in this command, integrity constraints will not be removed.
6.	In the DROP command, undo space is not used.	While in this command, undo space is used but less than DELETE.
7.	The DROP command is quick to perform but gives rise to complications.	While this command is faster than DROP.