Tuesday, June 22, 2010

What is the Difference between TRUNCATE and DELETE commands?

DELETE & TRUNCATE both are intended to delete data from the database tables. Both the commands can only remove the data from the tables but can not remove the table from the database - in other words, can't drop the structure of the table. Since both commands are used only to delete the data from the table, there should be obvious differences between thsese commands in aspects like T-SQL syntax, behavious, performance etc. Below are the differences between DELETE & TRUNCATE commands:

DELETE
  • T-SQL Command: 
  • DELETE FROM [TableName] WHERE [Condition]
  • DELETE is DML command hence it can be rolled back.
  • A WHERE clause can be used to provide the facility of conditional-based deletion
  • Triggers can be fired
  • When we use delete command on a table having IDENTITY column, it will not reset the counter of  identity column. Instead, it maintains the same counter for new rows.
  • DELETE is slower than Truncate because it logs deleted data into transaction log files. DELETE command removes one row at a time and logged into transaction log files. 

TRUNCATE
  • T-SQL Command:
  • TRUNCATE TABLE [TableName]
  • TRUNCATE is DDL command hence it can't be rolled back.
  • A WHERE clause can't be used for conditional-based deletion. It removes entire rows at once.
  • Triggers will not be fired - because Truncate doesn't remove any row, it just deallocates the data pages
  • When we use truncate command on a table having IDENTITY column, it will reset the counter used by identity column to the seed value defined for the column.
  • TRUCATE is faster than DELETE because it removes data directly without copying into transaction log files. In fact, TRUNCATE is also a logged operation but it logs the deallocation of the data pages in which the data exists - means your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. Thats why it is faster than DELETE command.
  • Can't use truncate command if table has foreign key constraint, even if the referring table has no records. You will get below error:
  • Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
  • Can't use truncate command if table has a column that participate in Indexed view.

No comments:

Post a Comment