Truncate statement:

1. Deletion is very faster.  Almost no (very minimal) transaction log is produced during this operation.

2. Data pages which are used by the table are de allocated for further use by SQL Server in other operations.

3. Identity column value is reset from its original default position. For example if a table has an identity column and identity value start form 1 then after running truncate command the value start incrementing back from 1.

4. As wiping out rows are very faster, so the number of locks are low. Although during TRUNCATE operation table and page lock happens but no locking for each row basis.

5. TRUNCATE TABLE command does not support where clause, it also does not works if foreign key exists in the table. In addition, table participates in log shipping or replication also does not honor TRUNCATE table command.

6. Records removed by the TRUNCATE COMMAND cannot be restored even though the database recovery model is set to FULL.

7.   TRUNCATE statement also does not fire triggers.

 

DELETE Statement:

1. The DELETE statement removes rows one at a time. For each deleted row the operation writes an entry in the transaction log.

2. DELETE operation is more resource intensive thus consumes more database resources and locks.

3. Where clause can be included with the statement to restrict the number of affected rows.

4. Internally the DELETE operation does not cleanup rows immediately if the table has any index on it, The operation marks the affected rows "to be deleted". The marked records are known as GHOST RECORDS. Although these records are de allocated quickly by a background cleanup process for better performance.

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.