| |
|
7 difference between Truncate and delete
Classified Posted on 27 Jul 2011
Home >> DataBase >> sql questions >> 7 difference between Truncate and delete |
TRUNCATE is faster due to its usage of system and transaction log resources is less than DELETE.
delete is less faster then truncate.
One another reason why TRUNCATE is faster that itremoves the data by deallocating the data pages used to store the tables data and only the page deallocations are recorded in the transaction log.
DELETE removes rows in sequence means one at a time and stores an entry in the transaction log for each deleted row so it usage is greater then the truncate.
TRUNCATE removes all the rows from a table without effecting table structure, columns, constraints, indexes.But the counter used by an identity for new rows is reset to the seed for the column.
DELETE does not reset Identity property of the table.
We cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Delete is same as Truncate table but we can activates Triggers if defined on table.
DELETE can be used with or without a WHERE clause
In Truncate it is not applicable
TRUNCATE cannot be rolled back but we can do this by using TRANSACTION OR TRUNCATE.
DELETE can be rolled back.
TRUNCATE is a DDL Command.
DELETE is DML Command. | |
|
|
|
|