Delete vs Drop vs Truncate

Drop vs Truncate vs Delete

               

                (i)           Drop command drops data-defination along with data.

                                Truncate command releases the memory occupied by the relation (table).

                                Delete commands deletes all records / one record from the table.

               

                (ii)          Drop command is an DDL operation, hence it works on data-defination.

                                Truncate command is an DDL operation, hence it works on data-defination.

                                Delete command is an DML operation, hence it works on data.

               

                (iii)         Execution Process:

                                Drop > Truncate > Delete.

                                reason:               Drop deletes data-defination. Hence data is automatically erased.

                                                                Truncate performs drop operation and then recreates the table.

                                                                Delete operation will delete record by record. Hence its the slowest of all.