When it comes to deleting data from a table, there are two main options: TRUNCATE and DELETE. Both commands have their own advantages and disadvantages, so it's important to understand the differences between them before deciding which one to use. TRUNCATE is faster than DELETE, as it doesn't scan all records before deleting them. TRUNCATE TABLE locks the entire table to delete data from a table; therefore, this command also uses less transaction space than DELETE.
Unlike DELETE, TRUNCATE does not return the number of rows deleted from the table. TRUNCATE does not generate any rollback data, which makes it very fast. It simply deallocates the data pages used by the table. The Delete command is useful for deleting all or certain rows from a table specified by a Where clause.
The truncate command removes all rows from a table. We can't use a Where clause in this. The SQL Delete command places a lock on each row that requires deleting from a table. The SQL Truncate command places a table and page lock to delete all records.
Delete Command Log Entry for Each Deleted Row in the Transaction Log. The truncate command does not record entries for each deleted row in the transaction log. The Delete command is slower than the Truncate command. It's faster than the delete command. Delete and truncate both commands can be used to delete data from the table.
Delete is a DML command, while truncate is a DDL command. Truncate can be used to delete all data from the table without maintaining the integrity of the table. On the other hand, the delete statement can be used to delete the specific data. With the delete command we cannot bypass the integrity enforcement mechanisms. If we want to delete all records in a table, it is preferable to use TRUNCATE instead of DELETE, since the former is faster than the latter.
The TRUNCATE command is like a DELETE command without the WHERE clause with a much smaller safety net. Truncate is all or nothing: it will delete all rows from the table, so it may not be an option if you want to keep some rows. In other words, there are fewer records during a TRUNCATE because only page deallocations are recorded in the transaction log, while a DELETE FROM records the deletion of each row. Note that TRUNCATE locks the table, so obviously don't use this command on a table shared by many concurrent users. MySQL does not allow users to truncate the table referenced as FOREIGN KEY in another table. In SQL Server, it is possible to undo a truncation operation if it is within a transaction and the transaction has not been committed. Most sites and even Microsoft say that Truncate cannot be reversed, but in reality if it starts with a transaction, it can be reversed as a deletion statement.
I think Delete and Truncate can only be undone if the operation was executed in an explicit transaction. I recommend using a DELETE statement in all cases, except in those special circumstances that deserve a TRUNCATE. It's important to understand both commands and their differences before deciding which one to use.
Leave Reply