Difference between Trucate And Delete

Truncate is much faster
Truncate resets autoincrements
Truncate is not transaction safe - it will autocommit
Delete doesn't have to remove all rows

Delete allows you to use a WHERE clause so only certain rows are deleted. Truncate will remove all rows as well as resetting any auto_increment columns you may have.

Thanks & Regards,
Mani Kumar

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Thank you,
Govind

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

In addition to the above listed differences, there are some more differences between TRUNCATE and DELETE

1. TRUNCATE TABLE command cannot be used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers, whereas DELETE statement can be used for deleting child records if the parent table doesn't have referenced individual records.

2. TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.

3. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas. whereas the DELETE statement will not resets the IDENTITY values to SEED

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Mark as Answer if got clear idea/solution...

Post us back the issue/doubts if not resolved the issue

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Hi,

There are few difference between Truncate and Delete Statement.

Delete:
1) We can get deleted data by using logs
2) We can perform Where Clause.
3) it is DML Command

Truncate:
1) we can't able to get deleted data.
2) We can't able to perform where clause.
3) It is DDL Command.
4) It delete total data.

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator