The delete command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table, and there will be no data in the table after we run the truncate command.
TRUNCATE
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
- 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 the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. 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.
- Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
- TRUNCATE is a DDL Command.
- TRUNCATE resets the identity of the table.
DELETE
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- DELETE does not reset Identity property of the table.
- DELETE can be used with or without a WHERE clause
- DELETE activates Triggers if defined on the table.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset the identity of the table.
No comments:
Post a Comment