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
For any query you can send mail at firstname.lastname@example.org