Difference between DELETE and TRUNCATE Command
DELETE Comand Vs. TRUNCATE Command
TRUNCATE command :
- TRUNCATE command is faster in compare to DELETE command as it uses fewer system & the transaction log resources .
- This commands removes all the data by deallaocating the data pages which are used to store the table’s data & only page deallocations are recorded/ stored in the transaction log.
- This command will remove all the rows from table but having table structures ,its constraints, columns, indexes and so on … remains. The columns used by an identity for new rows is reset to the seed for the column.
- You can not use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint because table i.e.TRUNCATE is not logged, as it can’t activate the trigger.
- This command can’t be rolled back.
- TRUNCATE is DDL type of command.
- This command resets identity of the table.
- WHERE clause can’t be used with TRUNCATE command .
DELETE Command :
- DELETE Command removes rows on at a time and records and entry in the transaction log for each deleted row.
- IF you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.
- DELETE can be used with or without a WHERE clause.
- DELETE activates triggers.
- DELETE can be rolled back.
- DELETE is DML command.
- DELETE does not reset identity of the table.