Friday, March 18, 2011

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

Answer:

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.


    Regards,

    View my 21 important .NET interview questions and answers

No comments: