Difference between delete and truncate

Written By:- Isha Malhotra

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