Difference between TRUNCATE and DELETE statement in SQL Server

What is “Difference between TRUNCATE and DELETE statement in SQL Server” is most common question asked in the interview. I have observed that the answer of this question is varies based on experience and understanding of the SQL server. So in this article I am explaining the significant difference between Delete vs Truncate.
  

When to use TRUNCATE: 

When you TRUNCATE a table less information is logged.  This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted. This means, that you need to be very careful when using the command (actually be careful with DELETE as well!).

Though you are able to rollback a TRUNCATE command in SQL Server, you can not do the same in Oracle.

The TRUNCATE command is simple yet extremely dangerous.  Here is an example to remove all rows from the employee table:

TRUNCATE TABLE employee

If you mistakenly execute a TRUNCATE statement, it is much more difficult to recover, and you may loose data in the process.  The TRUNCATE command does log the pages it removes, so it is possible to recover the pages using some advanced code.

Here are some reasons to use TRUNCATE:

1.   You want to “reset” a table to its empty state. All rows are removed, and identity key values reset to the initial defined values.

2.   You need to have a super quick way of clearing out table data. I can see this occurring when you need to repeatedly import test data or you have routines that use work tables or scratch tables to store information.

3.   You want to remove rows from a table without activating the table’s after delete trigger.

Keep in mind that TRUNCATE will lock the table, so obviously don’t use this command on a table being shared by many concurrent users.

When to use the DELETE command :

The DELETE command is used to remove records from a database.  It is the most common way to do so.  In its simplest form you can remove all the rows from a database or you can add a WHERE clause to remove only those meeting the criteria.

When execute the DELETE command,the DBMS logs all removed rows.  This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE.

The command

DELETE FROM employee

Will remove all employees from the employee table; whereas,

DELETE FROM   employee

WHERE  firstName = ‘Kris’

deletes all employees whose first name is Kris.

I would pretty much recommend using a DELETE statement in all cases, except for those special circumstances that merit a TRUNCATE.

Here are some things that happen during a DELETE that don’t during the TRUNCATE:

1.   Any deletion triggers are executed on the affected table.

2.   You are allowed to DELETE records that have foreign key constraints defined. A TRUNCATE cannot be executed if these same constraints are in place.

3.   Record deletions don’t reset identity keys. This is important when you need to guarantee each row uses a key that has never been used before.  Perhaps, this need to happen for audit reasons.

4.   Depending on the locking you are using, row locks are placed on deleteddeleted rows. Unaffected rows remain unlocked.

Conclusion

I should point out that TURNCATE is considered a DDL command; whereas, DELETE is DML.  I think this distinction should help you further understand when to use either command and the implications for doing so.

In a nutshell use DELETE to remove one or more rows from a table.  Only in special situation, such as when you need to reset a table to its initial state should you consider TRUNCATE.

TRUNCATE vs DELETE

TRUNCATE	DELETE
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
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.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
Identify column is reset to its seed value if table contains any identity column.	Identity of column keep DELETE retain the identity
To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
Truncate cannot be used with indexed views	Delete can be used with indexed views
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	Keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions. When we run truncate command to remove all rows of table then it actually doesn’t removes any row, rather it deallocates the data pages. In case of Truncate triggers will not be fired because no modification takes place, we have just deallocated the data pages not deleted any row from table.	Delete activates a trigger because the operation are logged individually. When we execute Delete command, DELETE trigger will be initiated if present. Delete is a DML command and it deletes the data on row-by-row basis from a table. Which means delete is modifying the data by deleting it from the table. Triggers are fired when a DML statement executed on a table, so trigger will be fired in case of Delete command execution.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.

If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.