Delete vs Truncate

Posted September 12, 2022 by Rohith ‐ 2 min read

Both the Truncate statement and the Delete statement are included in the category of SQL queries for deleting the data stored in a table. They carry out deletion operations on records or rows of a table that are no longer needed.

Difference between Delete and Truncate are stated below

Basis of ComparisonDELETETRUNCATE
DefinitionThe DELETE command in SQL removes one or more rows from a table based on the conditions specified in those rows.SQL’s TRUNCATE command is used to purge a table of all of its rows, regardless of whether or not any conditions are met.
LanguageIt is a DML(Data Manipulation Language) command,It is a DDL(Data Definition Language) command.
CommitIt is necessary for us to make a manual COMMIT after making changes to the DELETE command.When you use the TRUNCATE command, your modifications are committed for you automatically.
ProcessIt deletes rows one at a time and applies criteria to each deletion.It purges all of the information in one go.
ConditionThe WHERE clause serves as the condition in this case.The WHERE clause does not serves as the condition in this case.
LockAll rows are locked after deletion.It does this by utilizing table lock, which locks the pages so they cannot be deleted.
LogIt makes a record of each and every transaction in the log file.The only activity that is recorded is the deallocation of the pages on which the data is stored.
Transaction SpaceIn comparison to the TRUNCATION command, it consumes a greater amount of transaction space.In comparison to the DELETE command, it makes less use of the transaction space.
IdentityIf there is an identity column, the table identity is not reset to the value it had when the table was created.It returns the table identity to the value it was given as a seed.
PermissionIt requires authorization to delete.It requires table alter permission.
SpeedWhen it comes to large databases, it is much slower.It is faster, or more accurately, instant.
quick-references blog delete truncate differences

Subscribe For More Content