Thursday, October 16, 2008

Difference between DELETE and TRUNCATE

1. DELETE is a DML command. TRUNCATE is a DDL command.

2. After DELETE we can rollback the records. After TRUNCATE we cannot rollback the records.

3. We can use WHERE Clause with DELETE Command. We can't use WHERE Clause with TRUNCATE command. TRUNCATE delete all rows from the table.
4. After using DELETE Command the memory occupied not released untill the user gives COMMIT. After using TRUNCATE Command the memory realeased immediately.

5. TRUNCATE do not check constraints. DELETE checks constraints.
6. TRUNCATE is faster than DELETE and uses Less transaction log space. DELETE removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE removes the data by deallocating all data pages used to store the table data and records only the page deallocations in the transaction log.

7. DELETE can activate trigger. TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.

8. TRUNCATE reset the counter used by an identity column. DELETE retain the identity counter.

9. TRUNCATE cannot be used with tables referenced by foreign keys.

No comments: