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:-
|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.|
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- Difference between DELETE, DROP and TRUNCATE
- SQL | DROP, TRUNCATE
- Difference between DELETE and TRUNCATE
- Difference between DELETE and DROP in SQL
- Neo4j Drop Index
- SQL | ALTER (ADD, DROP, MODIFY)
- MySQL | DROP USER
- Create, Alter and Drop schema in MS SQL Server
- Difference between IBM DB2 and MS SQL
- Difference between PCI and PCI-X
- What's difference between MMU and MPU?
- Difference between PCA VS t-SNE
- Difference between H.323 and SIP
- Difference between AIX and iOS
- Difference between AIX and OS/2
- Difference between ELT and ETL
- Difference between BFS and DFS
- Difference between AIX and IBM i
- Difference between AIX and HP-UX
- Difference between AIX and QNX
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
Improved By : MKS075