Delete Drop Truncate Difference In Sql

Posted on by



1. DELETE :
Basically, it is a Data Manipulation Language Command (DML). It is use to delete the one or more tuples of a table. With the help of “DELETE” command we can either delete all the rows in one go or can delete row one by one. i.e., we can use it as per the requirement or the condition using Where clause. It is comparatively slower than TRUNCATE cmd.

Oracle database pl/sql sql DBA script, unix, shell script, Tips IT, Programming Home Oracle DBA & All IT » Function, Oracle Tips, Tuning Performance » ความแตกต่างระหว่างคำสั่ง Truncate, Delete และ Drop.

  • SYNTAX –
    If we want to delete all the rows of the table:
  • SYNTAX –
    If we want to delete the row of the table as per the condition then we use WHERE clause,
  1. DELETE and TRUNCATE are two SQL commands used to remove records from a particular table. But they differ in how they execute and operate. Removes Some or All rows from a table. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed. Causes all DELETE triggers on the.
  2. Truncate is an alternative of Delete in the sense that it allows Deletion of rows without losing the table structure but UNLIKE Delete, operation can't be undone. Drop statement however, removes the table completely along with its structure from the database and this operation can't be undone.

Note –
Here we can use the “ROLLBACK” command to restore the tuple.

2. DROP :
It is a Data Definition Language Command (DDL). It is use to drop the whole table. With the help of “DROP” command we can drop (delete) the whole structure in one go i.e. it removes the named elements of the schema. By using this command the existence of the whole table is finished or say lost.

Delete Drop Truncate Difference In Sql Query

Sql
  • SYNTAX –
    If we want to drop the table:
Delete Drop Truncate Difference In Sql

Note –
Here we can’t restore the table by using the “ROLLBACK” command.

3. TRUNCATE :
It is also a Data Definition Language Command (DDL). It is use to delete all the rows of a relation (table) in one go. With the help of “TRUNCATE” command we can’t delete the single row as here WHERE clause is not used. By using this command the existence of all the rows of the table is lost. It is comparatively faster than delete command as it deletes all the rows fastly.

  • SYNTAX –
    If we want to use truncate :

Note –
Here we can’t restore the tuples of the table by using the “ROLLBACK” command.

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.

Recommended Posts:

Delete Drop Truncate Difference In Sql

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Homeguard program blocker.

Delete Drop Truncate Difference In Sql Queries

Please Improve this article if you find anything incorrect by clicking on the 'Improve Article' button below.

Sql Truncate Vs Delete