Tuesday, July 7, 2015

Difference between DELETE, TRUNCATE command in SQL Server

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: 
  1. It Removes  rows from a table.
  2. 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.
  3. It removes rows row-by-row one at a time and records an entry in the Transaction logs.
  4. Delete use the @@ROWCOUNT function to return the number of deleted rows after operation.
  5. This is a DML category, it does not change any property of a table.
  6. IDENTITY columns are not re-seeded on this operation.
  7. When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. 
 TRUNCATE:
  1. It removes All rows from a table.
  2. WHERE clause we cannot use here, so it will not filter rows.
  3. IDENTITY columns will re-seeded on this operation.(After executing Truncate, the identity  value   will be reset from 1 by default)
  4. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
  5. This is a DDL command de-allocates Data Pages and empty them for use of other objects in the database.
  6. TRUNCATE TABLE always locks the page or schema (not each row) when it is executed.

No comments:

Post a Comment