One major difference(its partially myth) between truncate and delete is that truncate can't be rolled back while delete can be rolled back.
But its a myth .Truncate can also be rolled back but only if it is in Transaction statement.I will try to explain with some example .
Lets a table users.
Case 1:
BEGIN TRAN
TRUNCATE TABLE users
ROLLBACK
END TRAN
Now execute -
SELECT * FROM Users ; will return all data .
Obviously DELETE will return same result means all data will be returned.
Case 2:
Now execute
TRUNCATE TABLE users
and then execute immediately
ROLLBACK (here rollback without begin transaction)
Then execute
SELECT * FROM Users ; NO DATA will be returned,means here table is not rolled back
But DELETE can be rolled back in this situation
Now execute
DELETE TABLE users
and then execute immediately
ROLLBACK (here rollback without begin transaction)
Then execute
SELECT * FROM Users ; All data will be returned i.e. table rolled back successfully.
Conclusion:
TRUNCATE can be rolled back when used with in TRANSACTION,while DELETE can be rolled back either used within transaction or outside transaction block.
