Friday, June 17, 2011

Delete VS Truncate

"What is deference between Delete & Truncate ? " 

We all face this question in interview at least once in our career. Actually it is very tricky question. We have many misunderstanding regarding this two terms. After lots of searching today I found some good comparison between them. It is as per bellow.

Delete Truncate
Removes rows from a table or view. DELETE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Removes all rows from a table without logging the individual row deletions.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
You may use DELETE statement against a view (with some limitations). You can’t use TRUNCATE statement against a view.
You can specify a WHERE clause in a DELETE FROM statement-it is all or selected records. You can not specify a WHERE clause in a TRUNCATE TABLE statement. It is all or nothing
Does not reset identity value of the table. Reset identity value of the table.
Records removed using DELETE can be roll backed and can be restored point in time. Records removed using TRUNCATE can ALSO be roll backed.
Triggers get executed on Delete statement. Triggers does NOT executed on TRUNCATE statement.
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. 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.
DELETE statement can be used even if you have Replication/CDC enabled for the table. TRUNCATE statement can NOT be used if you have Replication/ CDC enabled for the table.
DELETE permissions default to members of the sysadmin fixed server role, the db_owner and db_datawriter fixed database roles, and the table owner.
Minimum permission required is DELETE permissions on the target table. SELECT permissions are also required if the statement contains a WHERE clause.
You need to be db_owner, ddl_admin, or owner of the table to be able to fire a TRUNCATE statement.
Minimum permission required is ALTER table.
DELETE is classified as DML operation. TRUNCATE is classified as DDL operation.
Special thanks to Rakesh Mishra for provide good compression

1 comment:

  1. this comparison helps to make proper use of both in SQL. good one yaar..

    ReplyDelete