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 Comparison | DELETE | TRUNCATE |
|---|---|---|
| Definition | The 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. |
| Language | It is a DML(Data Manipulation Language) command, | It is a DDL(Data Definition Language) command. |
| Commit | It 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. |
| Process | It deletes rows one at a time and applies criteria to each deletion. | It purges all of the information in one go. |
| Condition | The WHERE clause serves as the condition in this case. | The WHERE clause does not serves as the condition in this case. |
| Lock | All rows are locked after deletion. | It does this by utilizing table lock, which locks the pages so they cannot be deleted. |
| Log | It 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 Space | In 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. |
| Identity | If 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. |
| Permission | It requires authorization to delete. | It requires table alter permission. |
| Speed | When it comes to large databases, it is much slower. | It is faster, or more accurately, instant. |