Difference between Trucate And Delete

Posted by Kasani007 under Sql Server on 8/11/2014 | Points: 10 | Views : 711 | Status : [Member] | Replies : 5
Difference between TRUNCATE TABLE tablename and DELETE * FORM tablename ?




Responses

Posted by: Manimaddu on: 8/11/2014 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: kgovindarao523-21772 on: 8/12/2014 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/12/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Bandi on: 8/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Naveenhcl on: 9/3/2014 [Member] Starter | Points: 25

Up
0
Down
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

Login to post response