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
|
Tuesday, August 21, 2012
Difference Between TRUNCATE & DELETE
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment