Difference Between Truncate And Delete

Hi Ajay,

TRUNCATE

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the
page deallocations are recorded in the transaction log.
TRUNCATE 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.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.

DELETE

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
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.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.

Thanks

Hi Ajay .,
Delete commanand removes the rows from a table on the basis of the condition that we provide with a where clause.
delete removes rows one at a time and records an entry in the transaction log for each deleted row.
delete is DML command


Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
Truncate is faster and uses fewer system and transaction log resources than DELETE.
Truncate is DDL Command

Hi Ajay,

Delete statement removes rows of a table one by one & delete triggers on that table fires. 

But Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log.

 

Cheers

Hi Ajay,

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause

Thanks & Regards
----------------
Chintan Rathod

Dear

Delete Table means remove record from tabale and truncate means it set like a new table



like you have a identity column Companyid now Companyid is 27 you delete record  after delete when you insert the record it will take Companyid = 28 in delete case if you truncate the table it will recreate the table when you insert the record you will get  Companyid = 1