What is difference between DELETE & TRUNCATE commands?
This is most common interview question related to SQL and Database, every programmer must know differnce between Delete and Truncate.
Delete command removes the rows from a table based on the condition that we can write WHERE clause in delete command.
Truncate command will actually remove all the rows from a table, we can not use where clause in traincate command, there is no log maintains for this.
Blow are some differnce between Delete and Truncate.
- TRUNCATE Resets identity of the table.
- TRUNCATE is faster then Delete beacuse Truncate uses fewer system and transaction log resources than DELETE.
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
- It can not activate any trigger, because TRUNCATE TABLE is not logged.
- TRUNCATE can not be Rolled back using logs.
- TRUNCATE is DDL Command.
- DELETE is DML Command
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- If you want to retain the identity counter, use DELETE instead.
- DELETE Can be used with or without a WHERE clause
- DELETE Activates Triggers.
- DELETE Can be Rolled back using logs.
- DELETE does not reset identity of the table.