What is the difference between DELETE and TRUNCATE?

 Posted by ArticlesMaint on 10/3/2009 | Category: Sql Server Interview questions | Views: 11849

Following are difference between them:

• DELETE TABLE: - syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.

• DELETE table can be rolled back while TRUNCATE cannot be.

• DELETE table can have criteria while TRUNCATE cannot.

• TRUNCATE table cannot have triggers.

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Puneet20884 on: 12/23/2009

Deletes 1 or all or qualifying records from a table.
If the last record is Deleted, the Next record if having any Identity column will have that row's that column's value next to the deleted one.

Deletes all the records from the table.
New row inserted will have the Starting identities values in the columns specified for identity.
Posted by: Jatinnahar15 on: 4/15/2011
Posted by: Sudhir.gupta on: 1/2/2012
Both, DELETE table and TRUNCATE table data can be rolled back. Verified in MS SQL Server 2008
Posted by: Yjreddy on: 12/31/2012
i have verified on both truncate and delete...both truncate and delete can be rolled back...plz correct it

Login to post response