Truncate vs Delete

Thursday, 24 November 2011

Truncate vs Delete


Delete

1.DELETE is a DML command.
2.DELETE statement is executed using a row lock, each row in the table 
  is locked for deletion.
3.We can specify filters in where clause.It deletes specified data if 
  where condition exists.
4.Delete activates a trigger because the operation are logged 
  individually.
5.Slower than truncate because, it keeps logs
6.DELETE retain the identity value of column.
7.After a DELETE statement is executed, the table can still contain 
  empty pages.
8.The DELETE statement removes rows one at a time and records an entry
  in the transaction log for each deleted row.
9.DELETE statement can be used even if you have Replication/CDC 
  enabled for the table.
11.You can use DELETE statement on a parent table and if CASCADE ON
  DELETE is enabled then data from child table also get deleted. If
  CASCADE ON DELETE is NOT enabled and any of the child table has 
  related then you can’t delete records from parent table.
Truncate

1.TRUNCATE is a DDL command. 
2.TRUNCATE TABLE always locks the table and page but not each row.
3.Cannot use Where Condition.
4.TRUNCATE TABLE cannot activate a trigger because the operation does
 not log individual row deletions.
5.Faster in performance wise, because it doesn’t keep any logs.
6.If the table contains an identity column, the counter for that 
  column is reset to the seed value that is defined for the column.
7.TRUNCATE TABLE removes the data by deallocating the data pages
  used to store the table data and records only the page deallocations
  in the transaction log. 
8.In truncate table the counter used by an identity column for new rows
 is reset to the seed for the column.
9.TRUNCATE statement can NOT be used if you have Replication/ CDC enabled
 for the table.
10.You can’t truncate a parent table irrespective of whether the child
 table has any record or not. Truncate table statement will fail for
 parent table even if CASCADE ON DELETE is enabled.
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication. Delete works at row level, thus row level constrains apply

No comments:

Post a Comment