Difference Between Truncate and Delete

truncate-delete







Delete Truncate
The DELETE command is used to delete specified rows(one or more). While this command is used to delete all the rows from a table.
It is a DML(Data Manipulation Language) command. While it is a DDL(Data Definition Language) command.
There may be a WHERE clause in the DELETE command in order to filter the records. While there may not be WHERE clause in the TRUNCATE command.
In the DELETE command, a tuple is locked before removing it. While in this command, the data page is locked before removing the table data.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
DELETE command is slower than TRUNCATE command. While the TRUNCATE command is faster than the DELETE command.
To use Delete you need DELETE permission on the table. To use Truncate on a table we need at least ALTER permission on the table.
The identity of the fewer column retains the identity after using DELETE Statement on the table. Identity the column is reset to its seed value if the table contains an identity column.
The delete can be used with indexed views. Truncate cannot be used with indexed views.
This command can also active trigger. This command does not active trigger.
DELETE statement occupies more transaction spaces than Truncate. Truncate statement occupies less transaction spaces than DELETE.


Post a Comment

0 Comments