Similarity
These both command will only delete data of the specified table, they cannot remove the whole table data structure.
Difference
- TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
- We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger.
- TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.
- We can use any condition in WHERE clause using DELETE but you can’t do it with TRUNCATE.
- If table is referenced by any foreign key constraints then TRUNCATE will not work.
WikiDBA 
Following statement does not hold correct: “In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE.”
DELETE is Fully logged, where every deleted row is logged separately, thus DELETE takes time. TRUNCATE is also a logged operation, here only data pages are logged instead of rows, thus is faster.
If you talk about rollback, both can be rollbacked if provided in transaction. If no transaction, then both cannot be rollbacked, and you’ve to restore the database.
LikeLike
Hi Manoj,
Its very nice to hear from you, heartily welcome your comment, here is some corrections as ,
TRUNCATE TABLE deletes all of data in the table but like DELETE, it does not delete one-by-one records, its simply deallocate data page. The allocations are unhooked from the table and put onto a queue to be deallocated by a background task called the deferred-drop task. The deferred-drop task does the deallocations instead of them being done as part of the regular transaction so that no locks need to be acquired while deallocating entire extents
DELETE and TRUNCATE both can be rollbacked if its used in a Transaction and that session is not closed. If query is just executed from SSMS,in case of DELETE, may be rollbacked after session closed, but for TRUNCATE – If session is closed, it can not be rollbacked. If Database is in FULL Recovery mode, it can be rollback any changes done by DELETE using Log files using point of time restore of log files. TRUNCATE can not be rollback using log files in full recovery mode.
After a TRUNCATE, none pages are left for the table and if due to any reason , an error with the TRUNCATE happend, it cannot be repaired except if you have done a full backup before the TRUNCATE.
Another things, Truncate resets identity seed to the initial value.
LikeLike