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 “Truncate” →