What is difference between TRUNCATE and DELETE

         Are you working on databases or learning the database concepts, and want to know about the basic commands related to table like DELETE, TRUNCATE, then this is the right place. In this article, we are going to learn what is the meaning of these commands and the purpose of using these. TRUNCATE and DELETE command does the same job but slightly in a different manner. In this article, we will see what is the difference between the DELETE and TRUNCATE commands.

TRUNCATE command

- TRUNCATE command is DDL (Data Definition Language) command.

- If you use truncate command, then it will delete the data in a table and not a table itself.

- TRUNCATE locks the table but does not locks the rows, as it removes all the data from the table.

- It removes all the rows of the table, but the structure of the table remains as it is. It does not delete the   

   table structure, columns, indexes and constrains.

- This command does not require where clause.

- After truncate operation rollback process is not possible because this command does not maintain any log file from which we can rollback the data.

                                •  Syntax –

                                TRUNCATE TABLE table_name;

                                 Example –

                                 TRUNCATE TABLE Customer;

DELETE command

-                           DELETE command is DML(Data Manipulation Language) command.

-                          DELETE command deletes all the records from a table.

-                          DELETE locks the rows, for deletion each row in the table is locked.

-                         If we want to delete a specific row/record from a table then we can use the WHERE clause.

-                       The table structure, indexes, attributes will not be deleted after the DELETE operation.

-                        Rollback operation can be possible after DELETE operation but we have to rollback the data before the COMMIT statement. After the COMMIT statement we can not rollback the data.

 

·                                                                   •  Syntax –

                         DELETE FROM table_name;

                                     Example -

                         DELETE FROM Customer;

 

                                    Syntax (Using WHERE clause)-

                        DELETE FROM table_name WHERE condition;

                                    Example-

                                    DELETE FROM Customer WHERE id=1;

 The above delete operation will delete all the records from the ‘Customer’ table without deleting the ‘Customer’ table itself.

Difference between TRUNCATE and DELETE command

-                   DELETE command deletes the specific commands based on conditions defined by the WHERE clause,        but it does not free up the space.

-                 TRUNCATE command does not require a WHERE clause. After executing the TRUNCATE statement it releases all the memory along with the removal of data.

-                DELETE command maintains the log but the TRUNCATE command does not maintain the logs, hence the TRUNCATE command is faster than the DELETE command.

-               TRUNCATE command uses less Transaction space.