Home »
SQL
Difference between Drop and Truncate Command in SQL
Learn: What is SQL - DROP command and SQL - TRUNCATE command? What are the differences between them?
SQL - DROP Command
- DROP is a Data Definition Language (DDL) command.
- Data Definition Language commands refer to edit/remove structure/schema.
- In case of drop command where clause cannot use because it deletes the entire rows at a time, it provides no such facility to delete a single row from a table and the important thing is - it deletes table structure/schema/existing relationship/constraint.
SQL DROP Query
Display the table record:
mysql> select * from student;
Result:
Sid Sname Fees
102 Rahul 1000
103 Preeti 2000
3 rows in set (0.00 sec)
Query to DROP
mysql> drop table student;
Result:
Query OK, 2 row affected (0.02 sec)
Now, display the table record:
mysql> select * from student;
Empty set (0.00 sec)
Note:
- It is 20 times faster than truncate because it deletes all rows at the same time and does not create structure again (like truncate command) from a table.
- Basically drop command is popular to delete everything from a table like data/records/tuples, structure/schema, Relationship, Constraints etc.
- In case of drop command you cannot rollback.
SQL - TRUNCATE Command
- Truncate is a Data Definition Language (DDL).
- Here, Data Definition refers to edit/remove table’s structure/schema.
- In case of truncate command where clause cannot use because it deletes the entire row at a time it provides no such facility to delete a single row from a table.
SQL Query Examples:
Display the table record:
mysql> select * from student;
Result:
Sid Sname Fees
102 Rahul 1000
103 Preeti 2000
3 rows in set (0.00 sec)
Query to TRUNCATE
mysql> truncate student;
Result:
Query OK, 2 row affected (0.02 sec)
Now, display the table record:
mysql> select * from student;
Empty set (0.00 sec)
Note:
- It is 20 times faster than delete because it deletes all rows at a time from a table.
- Basically truncate command is popular to delete all rows simultaneously from a table.
- In case of truncate command you cannot rollback data means you cannot recover deleted data since last commit.