Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database /Difference Between Delete and Drop

Difference Between Delete and Drop

July 18, 2011Posted byAndrew

删除vs Drop

Both Delete and Drop commands belong to SQL (Structured Query Language) statements, and they are used in case of removing data from a database. Delete is a DML (Data Manipulation Language) command. It deletes some or all the data from a table according to the condition that the user has specified. Delete statement removes only the data records in the table, but the table structure presents same in the database. Drop command is a DDL (Data Definition Language) statement, and it acts in a different way from the Delete command. It is not a conditional based statement, so deletes entire data from the table, also it removes the table structure and all the references to that table permanently from the database.

删除Statement

如前所述,Delete语句删除数据from a table based on the condition provided, and the Where clause is used with Delete to specify this required condition. If the Where clause is not stated with Delete, all the table data is removed from the table. However, in the Delete operation, the existing table structure remains same. Therefore, user does not need to define the table structure if he/she wants to reuse the table again. As Delete is a DML command, it does not commit automatically after execution. So, this can be rolled back in order to undo the previous operation. Otherwise, the Commit statement should be called to make the changes permanent. While executing the Delete statement, it records an entry in the transaction log for each row deletion. So, this affects to slow down the operation. As well, it does not deallocate the space used after execution.

Following is the syntax for Delete statement.

DELETE FROM

or

DELETE FROM WHERE

Drop Statement

Drop statement removes not only all the table records from the database without any condition, but also it removes the table structure, integrity constraints, indexes, and access privileges of the relevant table from the database permanently. So, all the relationships for other tables also no longer exist, and information about the table is removed from the data dictionary. So, if the user wants to reuse the table he/she needs to define the table structure and all other references to the table again. Drop is a DDL command and after the execution of the command, it cannot be rolled back again, because Drop command uses an auto commitment. Therefore, user should be very careful in using this command. Drop statement cannot be applied on system tables, and also it cannot be used for the tables which have foreign key constraints.

Drop command can be used not only for SQL tables, but also for databases, views and table columns, and all the data stored in these objects are lost forever along with the objects.

Following is the typical syntax for Drop command.

DROP TABLE

What is the difference between Delete and Drop?

1. Delete and Drop commands removes table data from a database.

2. But Delete statement performs conditional based deletion, whereas Drop command deletes entire records in the table.

3. Also, Delete statement removes only the rows in the table and it preserves the table structure as same, whereas, Drop command removes all the data in the table and the table structure, also it removes all other references from the database.

4. Delete is a DML statement, whereas Drop is a DDL command. So, Delete operation can be rolled back and it is not auto committed, while Drop operation cannot be rolled back in any way as it is an auto committed statement.

5. Drop command cannot be used on the tables that have been referenced by foreign key constraints, whereas Delete command can be used instead of that.

6. Drop command should be used carefully with a good understanding compared to the Delete statement in SQL applications.

Related posts:

Difference Between Drop and Truncate Difference Between Update and Alter Difference Between Insert and Update and Alter Difference Between data centre and NOC Difference Between Foreign key and Primary keyDifference Between Foreign key and Primary key

Filed Under:DatabaseTagged With:Commit statement,DDL command,DDL statement,删除,删除command,删除statement,DML command,DML statement,Drop,Drop command,Drop statement,SQL statements,SQL tables,Where clause

About the Author:Andrew

Leave a ReplyCancel reply

Your email address will not be published.Required fields are marked*

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Corpse and Cadaver

Difference Between Corpse and Cadaver

Difference Between Leeks and Onions

Difference Between Kidney Beans and Red Beans

Difference Between Permittivity and Permeability

Difference Between Software Engineering and Systems Engineering

Latest Posts

  • What is the Difference Between Acne and Rosacea
  • What is the Difference Between Blood Transfusion and Dialysis
  • What is the Difference Between Language Acquisition and Language Learning
  • What is the Difference Between Heart Attack and Gastric Pain
  • 维生素和之间的区别是什么Amino Acids
  • What is the Difference Between Induction Cooker and Normal Cooker
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018Difference Between. All rights reserved.Terms of Useand Privacy Policy:Legal.