.NET and SQL Server interview question: - What is difference between truncate and delete?

 Posted by articlesmaint on 3/18/2011 | Category: .NET Framework Interview questions | Views: 2414

Both Truncate and Delete are used to delete data from the tables.  Below are some important differences.

·     TRUNCATE is a DDL (data definition language) statment whereas DELETE is a DML (data manipulation language) statement.

  • In DELETE command we can use where condition in TRUNCATE you cannot.

  • TRUNCATED data cannot be rolled back while delete can be.

  • When there are triggers on table and when you fire truncate trigger do not fire . When you fire delete command triggers are executed.

  • TRUNCATE resets the identity counter value where DELETE does not.

  • Delete and Truncate both are logged operation. Delete operation is logged on row basis while TRNCATE logs at page level.

  • TRUNCATE is faster than DELETE.



Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response