Difference between Drop and Truncate Command in SQL

Learn: What is SQL – DROP order and SQL – TRUNCATE order? What are the contrasts between them?

SQL – DROP Command

DROP is a Data Definition Language (DDL) order.

Information Definition Language orders allude to alter/evacuate structure/diagram.

If there should arise an occurrence of drop order where proviso can’t utilize on the grounds that it erases the whole columns one after another, it

gives no such office to erase a solitary line from a table and the significant thing is – it erases table structure/pattern/existing relationship/imperative.

SQL DROP Query

Show 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 multiple times quicker than shorten in light of the fact that it erases all columns simultaneously and doesn’t make structure once more (like shorten order) from a table.
• Essentially drop order is mainstream to erase everything from a table like information/records/tuples, structure/composition, Relationship, Constraints and so forth.

• In the event of drop order, you can’t rollback.

SQL – TRUNCATE Command

Shorten is a Data Definition Language (DDL).

• Here, Data Definition alludes to alter/expel table’s structure/mapping.
• In the event of shortening order where provision can’t utilize on the grounds that it erases the whole column at a time, it gives no such office to erase a solitary line from a table.

SQL Query Examples:

Show 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:

1. It is multiple times quicker than erase in light of the fact that it erases all lines one after another from a table.
2. Essentially shorten order is mainstream to erase all lines at the same time from a table.
3. If there should be an occurrence of shortening order you can’t rollback information implies you can’t recoup erased information since last submit.