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. |
0 Comments