Dec 232011
 

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

  1. Truncates reset the identity value
  2. Truncates can’t have where clause
  3. Truncates can’t be run on a table which is referenced by other tables
  4. Truncates do not fire triggers.
  5. Truncates can’t be run on a table which has depending objects such as indexed views.
  6. Truncates can’t be run on a table which has published by merge or transactional replication publication
  7. Truncates need db_owner and db_ddladmin permission.
  8. Truncates need locks on the table and schema but do not need locks on rows of the tables

Continue reading »