| Truncate | Delete |
| De allocation of datapages | Datapages are not deallocated |
| Where clause cannot be used in a truncate statement | Where clause can be used in a delete statement |
| Truncate will not work on tables referenced by one or more FOREIGN key constraint | Delete will work |
| When you truncate if the table contains an identity column then the counter for that column is reset to the seed value. | Identity counter is not resetted |
| Less transaction log space is used | It removes one row at a time and saves an entry into the transaction log for each deleted row |
| Each row is not locked when truncate is used, instead a table or a page is locked | When deleted using a row lock each row in the table is locked |
| Truncate cannot activate a trigger for a table | Delete can activate a trigger |
Tuesday, July 20, 2010
Difference between Delete and Truncate statement in SQL
The difference between TRUNCATE and DELETE are as follows
Subscribe to:
Posts (Atom)