Wednesday, February 1, 2012

What should we use delete or truncate

There are many stories about the differences between the delete and the truncate statement.

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


Reset the identity value
Can’t have where clause
Can’t be run on a table which is referenced by other tables
Does not fire triggers.
Can’t be run on a table which has depending objects such as indexed views.
Can’t be run on a table which has published by merge or transactional replication publication
Needs db_owner and db_ddladmin permission.
Needs locks on the table and schema but do not need locks on rows of the tables

Internally, after a table is truncated, no data pages are left for the table whereas deletion over a heap may not release the database and deletion on a B-Tree may not release none leaf pages. Truncating command simply unhook data from metadata of the table. When table is small, unhooked data (rows) will be deallocated synchronously. When table is large, data will be removed asynchronously which is called deferred deallocation.

Some myths about the truncate statement:

Truncate cannot be rolled back. That’s not true. Truncate Table command is transactional. It can be rolled back.
Truncate generate less log records. It depends. If table is small enough, truncating table will generate more logs.

Truncate is always faster. It depends. The Truncate statement needs more operations than delete if the table is very small, thus theoretically it needs more time to process.

Deferred deallocation

While a large table is getting truncated, SQL Server merely unhooks the meta-data and IAMs then returns without resetting bits in IAM, PFS, GAM and SGAM. An internal process will set those bits and then change the allocation related tables.

No comments:

Post a Comment