Posted in: PL/ SQL Tutorial,PL/ SQL Tutorial with Examples,SQL PL/SQL Questions
Truncate vs Delete :
1. DDL Command
2. Creates table level lock
3. Does not support Where clause, all of the data is deleted.
4. Does not log each row while deletion.
5. Can not be rollbacked
6. Faster in performance compared to delete command which is deleting all the records.
7. Triggers are not activated.
8. If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
Delete:
1. DML Command
2. Creates row level locks
3. Supports Where clause and hence conditional delete is possible.
4. Logs for each row being deleted.
5. Can be Rollbacked.
6. Triggers (if any) are activated for each delete row call.
7. Delete retains identity, means next time row is inserted the sequence of Auto Increment column will be maintained.