Difference between delete and truncate.

What is difference between delete and truncate

TRUNCATE

1. It is DDL command

2. Speed is faster

Reason: When you type DELETE, all the data is first copied into the Rollback Tablespace. Then the delete operation is performed. That's why, when you type ROLLBACK after deleting a table, you can get back the data. (The system gets it from the Rollback Tablespace.). All that processing takes time. But when you type TRUNCATE it removes the data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you truncate you can't get back the data.

3. Do not Check Constraints.

4. Roll back is not possible.

5. Cannot use with where clause.

6. When a table is truncated the memory occupied is released.

7. The truncate statement will result in clearing table spaces or memories and the table structure remain in the database. Therefore it frees table storage spaces; use it only when you need to remove all data from a table.

8. Removes the data by deallocating the data pages used to store the table's data and only the page deallocations are recorded in the transaction log.

9. Removes all rows from a table but the table structure and its columns constraints indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

10. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint;

11. Because TRUNCATE TABLE is not logged it cannot activate a trigger

DELETE

1. It is a DML command

2. Speed is slow

3. Check constraints, if exists then show error.

4. We can rollback.

5. User Where Clause

6. is slower than truncate as it is dml has to go through rollback segments

7. We can use where clause with delete. When a table is deleted memory occupied is not released and also the water mark is not adjusted.

8. The delete statement will result in the table spaces or memories would remain as the current size and table structure remain in the database.

9. It is slower than the truncate command.

10. Delete, use check constraints if exits then show error.

11. Removes rows one at a time and records an entry in the transaction log for each deleted row.

12. If you want to retain the identity counter use DELETE instead. If you want to remove table definition and its data use the DROP TABLE statement.

13. Activates Trigger

 http://vishalnayan.wordpress.com/