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.|
Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.
- 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 LED and LCD
- Difference between CRT and LCD
- What's difference between MMU and MPU?
- Difference between 1G and 2G
- Difference between CD and DVD
- Difference between Blu-ray and DVD
- Difference between ADO and ADO.NET
- Difference between SQL and T-SQL
- What is the difference between GUI and CUI?
- Difference between H.323 and SIP
- Difference Between Gi-Fi and Li-Fi
- Difference between T-SQL and PL-SQL
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