Both SQL (Structure Query Language) commands, Delete and Truncate are used to get rid of data stored in tables in a database. Delete is a DML (Data Manipulation Language) statement and it removes some or all the rows of a table. The ‘Where clause’ is used to specify the rows that are required to be deleted, and if the Where clause is not used with Delete statement, it removes all the data in the table. Truncate is a DDL (Data Definition Language) statement, and it removes entire data from the table. Both of these commands don’t destroy the table structure and the references to the table, and only the data is removed as needed.
Delete Statement
Delete statement allows the user to remove data from an existing table in a database based on a specified condition, and the ‘Where clause’ is used for determining this condition. Delete command is referred as a logged execution, because it deletes only one row at a time, and keeps an entry for each row deletion in the transaction log. So, this causes to make the operation slower. Delete is a DML statement, and so it is not automatically committed while executing the command. Therefore, Delete operation can be rolled back to access the data again, if required. After execution of Delete command, it should be committed or rolled back in order to save changes permanently. Delete statement doesn’t remove the table structure of the table from the database. Also it does not deallocate the memory space used by the table.
The typical syntax for Delete command is stated below.
DELETE FROM <table_name>
or
DELETE FROM <table_name> WHERE <condition>
Truncate Statement
Truncate statement removes all the data from an existing table in a database, but it preserves the same table structure, also the integrity constraints, access privileges and the relationships to other the tables. So, it is not required to define the table again, and the old table structure can be used, if the user wants to reuse the table again. Truncate removes entire data by deallocating the data pages used to keep data, and only these page deallocations are kept in the transaction log. Therefore, truncate command utilizes only a fewer system and transaction log resources for operation, so it is faster than other related commands. Truncate is a DDL command, so it uses auto commitments before and after execution of the statement. Hence, truncate cannot roll back the data again in any way. It releases memory space used by the table after the execution. But Truncate statement cannot be applied on the tables that are referenced by foreign key constraints.
Following is the common syntax for Truncate statement.
TRUNCATE TABLE <table_name>
|
What is the difference between Delete and Truncate? 1. Delete and Truncate commands remove data from existent tables in a database without harming the table structure or other references to the table. 2. However, Delete command can be used to delete specific rows only in a table using a relevant condition, or to delete all the rows without any condition, while the Truncate command can be used only for deleting entire data in the table. 3. Delete is a DML command, and it can roll back the operation if necessary, but Truncate is a DDL command, so it is an auto commit statement and cannot be rolled back in any way. So it is important to use this command carefully in database management. 4. Truncate operation consumes fewer system resources and transaction log resources than the Delete operation, therefore, Truncate is considered as faster than Delete. 5. Also, Delete does not deallocate space used by the table, whereas Truncate frees the space used after execution, so Delete is not efficient in case of deleting the entire data from a database table. 6. However, Truncate is not allowed to use when the table is referenced by a foreign key constraint, and in that case, the Delete command can be used instead of Truncate. 7. Finally, both of these commands have advantages and also disadvantages in applying them in Database Management Systems and the user should be aware of using these commands appropriately to achieve good results.
|