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 CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- SQL | DROP, TRUNCATE
- Difference between DELETE, DROP and TRUNCATE
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- Difference between DELETE and DROP in SQL
- Difference between DELETE and TRUNCATE
- Create, Alter and Drop schema in MS SQL Server
- CREATE and DROP INDEX Statement in SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- SQL | ALTER (ADD, DROP, MODIFY)
- Drop login in SQL Server
- DROP SCHEMA in SQL Server
- TRUNCATE() Function in MySQL
- SQL | Procedures in PL/SQL
- DROP FUNCTION and its Parameters
- Difference between Static and Dynamic SQL
- Difference between Simple and Complex View in SQL
- Difference between SQL and NoSQL
- Difference between SQL and PLSQL
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