This operation doesn’t affect the table schema.
Advantages when compared to Delete operation:
- Faster and uses less resources.
- Doesn’t log each row that’s deleted.
- Requires fewer locks – locks are obtained for the whole table and the page but not on each row.
- If table contains an identity column, the corresponding counter is reset to the seed value for the column.
- After truncate operation is completed, zero pages are left in the table without any exception.
- After truncate, the pages can be immediately reused in other areas.
- Doesn’t allow WHERE condition.
- Cannot be used if table has foreign key constraints.
- Cannot be used if the table participates in an indexed view.
- If the table is published using transactional replication or merge replication.
- Cannot activate triggers because row level locks are not used. On Update,Delete,Insert triggers won’t fire
- Rows deleted cannot be restored.
This operation doesn’t affect the schema.
DELETE FROM TABLE achieves the same result as Truncate but in a less efficient way.
- Locks each individual row for deletion and so rows deleted can be restored if needed.
- Logs each row that has been deleted.
- Retains the counter value for Identity column.
- Allows WHERE condition.
- Slow and uses more resources
- Empty pages cannot be reused until a background cleanup process de-allocates the pages.
Deletes the data as well as the schema.
- Cannot be used on a table with foreign key constraint