Delete Vs. Truncate Vs. Drop

What is the Difference between TRUNCATE, DELETE and DROP Commands?

DELETE

• Delete is a DML Command as it is just used to manipulate/modify the table data. It does not change any property of a table.

• The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed.

• After performing a DELETE operation, you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

• It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.

• Every deleted row in locked, thus it requires more number of locks and database resources.

• This operation will cause all DELETE triggers on the table to fire.

SYNTAX:

To Delete a particular row:

DELETE FROM <TableName>

WHERE <ColumnName> = <ColumnValue>;

To Delete all rows

DELETE FROM <TableName>;

Let’s see how Delete Command works.

 SQL> SELECT * From TEST;  
     ID NAME      SALARY STATUS     DEPARTMENT  
 ---------- ---------- ---------- ---------- --------------------  
     102 Ankit      8000 Active     CISCO  
     104 Nikhil     69000 Active    Retail  
     105 Rajan      18000 InActive  Insurance  
     107 Karan     101000 Active    BFSI  
     110 Sajal      88000 InActive  BFSI  
     101 Ravi      89000 Active     BFSI  
     109 Manu      777000 InActive  CISCO  
     103 Tom       5600 Active      SFDC  
     106 Tomy       7700 InActive   SFDC  
     108 Jack      88800 Active     Security  
 10 rows selected.  

SQL> DELETE From TEST Where DEPARTMENT='CISCO';  
 2 rows deleted.  

 SQL> DELETE From TEST;  
 8 rows deleted.  

 SQL> SELECT Count(*) FROM TEST;  
  COUNT(*)  
 ----------  
      0  

 SQL> Rollback;  
 Rollback complete.  

 SQL> SELECT Count(*) FROM TEST;  
  COUNT(*)  
 ----------  
     10  

TRUNCATE

• TRUNCATE is a DDL Command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.

• TRUNCATE removes all rows from a table.

• Does not require a WHERE clause, so you cannot filter rows while Truncating.

• TRUNCATE does not return number of deleted rows.

• The operation cannot be rolled back and no triggers will be fired because it does not operate on individual rows.

• TRUCATE is faster and doesn't use as much undo space as a DELETE.

• You cannot use conditions in case of truncate.

SYNTAX:
TRUNCATE Table <TableName>;

SQL> SELECT * From TEST;  
     ID NAME      SALARY STATUS     DEPARTMENT  
 ---------- ---------- ---------- ---------- --------------------  
     102 Ankit      8000 Active     CISCO  
     104 Nikhil     69000 Active    Retail  
     105 Rajan      18000 InActive  Insurance  
     107 Karan     101000 Active    BFSI  
     110 Sajal      88000 InActive  BFSI  
     101 Ravi      89000 Active     BFSI  
     109 Manu      777000 InActive  CISCO  
     103 Tom       5600 Active      SFDC  
     106 Tomy       7700 InActive   SFDC  
     108 Jack      88800 Active     Security  
10 rows selected.  

SQL> TRUNCATE Table TEST;  
 Table truncated.  

SQL> SELECT Count(*) FROM TEST;  
  COUNT(*)  
 ----------  
      0   

SQL> Rollback;  
 Rollback complete.  

SQL> SELECT Count(*) FROM TEST;  
  COUNT(*)  
 ----------  
      0  

DROP 

• Drop is a DDL command.

• The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.

• Some objects like Views, Stored Procedures that references the dropped table are not dropped and must be explicitly dropped.

• Cannot drop a table that is referenced by any Foreign Key constraint.

• No DML triggers will be fired.

• The operation cannot be rolled back.

SYNTAX:

Drop Table <TableName>;

SQL> SELECT * From TESTDATA;  
     ID NAME      SALARY STATUS     DEPTNO MANAGERID  
 ---------- ---------- ---------- ---------- ---------- ----------  
     102 Ankit      8000 Active        20    101  
     104 Nikhil     69000 Active       10    101  
     105 Rajan      18000 Active       20    112  
     107 Karan     101000 Active       20    112  
     110 Sajal      88000 Active       10    101  
 

SQL> DROP TABLE TESTDATA;  
 Table dropped.  

SQL> SELECT Count(*) From TESTDATA;  
     SELECT Count(*) From TESTDATA  
            *  
 ERROR at line 1:  
 ORA-00942: table or view does not exist  

SQL> Rollback;  
 Rollback complete.  

SQL> SELECT Count(*) From TESTDATA;  
     SELECT Count(*) From TESTDATA  
            *  
 ERROR at line 1:  
 ORA-00942: table or view does not exist  

NOTE- To Drop table with all its constraint and references you need to do the following-

Drop table <TBLName> Cascade Constraint Purge;

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.