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.
|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