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|
|DDL command||DML command|