If you wish to removes rows basis on condition then we have to use DELETE command while truncate will remove all the rows from a table.
- Delete is a DML command
- We can use WHERE clause in delete statement.
- Delete statement delete specific rows if where condition exist.
- Delete command lock the row when executed, each row in table is locked for deletion.
- Delete operation are logged individually so delete activates the trigger.
- Rollback is possible in delete.
- Delete keeps log so delete is slower then truncate.
- Truncate is DDL command.
- Truncate command removes all data from the table.
- Truncate does not keep the logs so it fast then DELETE.
- Truncate statement is not conditional mean we cannot use WHERE clause in truncate statement.
- Rollback is not possible.
- Truncate reset the table to its empty state.