|
Difference between Truncate and Delete in SQL
Truncate an Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure.Both statements delete the data from the table not the structure of the table.
- TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
- You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .
- You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.
- A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
- If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
- TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
- Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
- TRUNCATE is faster than DELETE.
Related Articles:
|
User Comments:
Comment By
Bart Czernicki
on
01/03/2009
The rollback comment is not 100% true. You can rollback truncate statements under certain conditions.
http://sql-server-performance.com/Community/forums/p/16968/99791.aspx
Comment By
WebDevVote.com
on
01/05/2009
Track back from http://webdevvote.com/AspNet/Difference_between_Truncate_and_Delete_in_SQL
Comment By
Experts Comment
on
09/26/2010
Below link provide good explanation for difference between Delete and Truncate
http://www.a2zmenu.com/MySql/difference-between-DELETE-and-TRUNCATE.aspx
Comment By
sandeep sharma
on
10/04/2010
diffrence between DELETE tablename
and DELETE from tablename
Comment By
pradeep
on
11/22/2010
very simple and easy way of learning.thanks
Comment By
Kumaresan
on
01/07/2011
You can rollback both delete and truncate.Don't misguide others.
Comment By
Bishnu Biswal
on
02/21/2011
TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter. very simple and easy way of learning.thanks
Comment By
jatin
on
04/15/2011
You will get better and easy explanation over here:
http://dotnetpeoples.blogspot.com/2011/04/difference-between-delete-and-truncate.html
Comment By
mallika
on
04/22/2011
thanks
Post Your Comment :
|