Difference Between DROP and TRUNCATE in SQL

Difference between drop and truncate in SQL:

Drop and Truncate are DDL(data definition language) commands. Now We are going see the Differences between Drop and truncate.

DROP:-   

Drop is one of the DDL command. Drop Command is used to drop the entire table and it's linked objects like index, trigger, constraint..etc.  

Once we execute the DROP command to drop a table, This will delete the object from database and release the memory since by default DDL commands are auto commit. So rollback command won't work.

But We have Recycle Bin concept in Oracle. So we can get it back by using this feature and also performance of drop is high But truncate performance is high compared to drop.

SYNTAX: 

DROP TABLE tablename;            

TRUNCATE:-

Truncate also a DDL command. Truncate command is used to delete all rows from a table. So system will delete all the data from table But Linked objects will be remain same(index, triggers...etc). Truncate won't delete the linked objects.

Like drop, Truncate also auto commit. So we can't get back the data once we executed. Here we don't have Recycle Bin to get the data. So we have to be sure before truncate.

SYNTAX: 

TRUNCATE TABLE tablename;     

 Below are the Difference's between DROP and TRUNCATE. 

1. DROP command drops entire table definition with objects(total content) where as TRUNCATE removes/delete all the rows from a table.

2. Both DROP and TRUNCATE are DDL commands.

3. Both have Auto commit functionality. So no need to commit explicitly.

4. Once we DROP the table,  can't able see the table and it's structure where as TRUNCATE keeps the table Structure.

5. DROP command deletes all the constraints where as TRUNCATE Won't deletes the Constraints.

6. Post DROP also we can get the object Back using recycle bin, But TRUNCATE deletes all rows permanently.

                                                    Thank You