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:

  1. It is 20 times faster than delete because it deletes all rows at a time from a table.
  2. Basically truncate command is popular to delete all rows simultaneously from a table.
  3. In case of truncate command you cannot rollback data means you cannot recover deleted data since last commit.





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.
Learn PCB Designing: PCB DESIGNING TUTORIAL







Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.