What are the differences between Delete and Truncate in SQL Server? This is one of the most frequently asked questions in SQL server interviews and the answer varies depending upon your experience and understanding of SQL Server. Here, I am listing the differences that I think are most significant.
| 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. |
| Log shipping has NO impact on DELETE. | Log shipping has NO impact on TRUNCATE. |
| 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. |
Regards
Rakesh Mishra
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
