Search This Blog

November 07, 2007

Difference between Delete and Truncate operation in MS SQL Server

Delete:

  • Used to remove all rows or specified rows based on where condition.
  • Logs in Transaction Log while removing each row. Transaction Log is more in delete operation.
  • We can Rollback the delete operation
  • Raise Triggers if present.
  • Delete is a DML(Data Manipulation Language) command

Truncate:

  • Truncate removes all rows in a table.
  • Truncate logs the erased page information in the Transaction Log. Transaction log size is small.
  • Truncate resets the seed value(initializes ) for the identity column
  • Truncate is faster than delete operation.
  • Triggers are not raised by Truncate operation.
  • Can Rollback the Truncate operation.
  • We cannot use Truncate on a table with Foreign Key

No comments: