Tuesday, August 21, 2012

Difference Between TRUNCATE & DELETE

TRUNCATE
DELETE
faster and uses fewer system and transaction log resources than DELETE.
 Slower than truncate because, it maintain logs for every record.
removes the data by deallocating the data pages used to store the tables data, and only the page deallocations are recorded in the transaction log.
removes  rows one at a time and records an entry in the transaction log for each deleted row.
removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint;
use DELETE statement without a WHERE clause
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
Activates Trigger
No Roll back
Can Rollback
DDL Command
DML Command

No comments:

Post a Comment