Delete V/S Truncate
Both commands are used to remove data from tables but they have following differences: -
- 1. Delete is DML command but Truncate is DDL command.
- 2. Delete command can be used with where clause if we need to delete specific row but truncate command doesn’t work with where clause so it removes all rows.
- 3. Truncate set the identity from the beginning but delete not.
- 4. Delete command delete row one by one and maintains its data in transaction log but truncate de allocates its data pages.
- 5. Delete is slower than truncate as it has to maintains its each entry in transaction log.
- 6. Delete can be roll backed but truncate not.
- 7. Delete command activates all triggers if any associated but truncate not.
Note: - delete and truncate both can be roll backed if used with transaction.
Syntax of delete
delete from emp where id=1
Syntax of truncate
truncate table emp