Define Truncate and Delete commands.
Truncate- Truncate command is used to remove all rows of the column.
- The removed records are not recorded in the transaction log.
- It is the fast way to remove all the records from the table.
- The records once removed can’t be rolled back.
- It can’t activate trigger.
- It resets the identity of the column.
Delete- Delete command removes records one at a time and logs into the transaction log.
- It can be used with or without where clause.
- The records can be rolled back.
- It activates trigger.
- It doesn’t reset the identity of the column.
Define Truncate and Delete commands.
| TRUNCATE | DELETE |
| This is also a logged operation but in terms of deallocation of data pages. | This is a logged operation for every row. |
| Cannot TRUNCATE a table that has foreign key constraints. | Any row not violating a constraint can be Deleted. |
| Resets identity column to the default starting value. | Does not reset the identity column. Starts where it left from last. |
| Removes all rows from a table. | Used delete all or selected rows from a table based on WHERE clause. |
| Cannot be Rolled back. | Need to Commit or Rollback |
| DDL command | DML command |
