"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.
|
this comparison helps to make proper use of both in SQL. good one yaar..
ReplyDelete