First of all DELETE and TRUNCATE are
used to remove records from a table in database. But if you look on their system
operation, you can get some difference.
DELETE:
- It Removes rows from a table.
- We can specify WHERE condition over DELETE command. If we don’t use WHERE condition, then it will remove all the rows from a table.
- It removes rows row-by-row one at a time and records an entry in the Transaction logs.
- Delete use the @@ROWCOUNT function to return the number of deleted rows after operation.
- This is a DML category, it does not change any property of a table.
- IDENTITY columns are not re-seeded on this operation.
- When the DELETE statement is executed using a row lock, each row in the table is locked for deletion.
TRUNCATE:
- It removes All rows from a table.
- WHERE clause we cannot use here, so it will not filter rows.
- IDENTITY columns will re-seeded on this operation.(After executing Truncate, the identity value will be reset from 1 by default)
- It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
- This is a DDL command de-allocates Data Pages and empty them for use of other objects in the database.
- TRUNCATE TABLE always locks the page or schema (not each row) when it is executed.
No comments:
Post a Comment