Truncate vs Delete

To remove data from database table, we can use Truncate or Delete command on database table.

There are many difference in Truncate and Delete command:

1. Clause:
You can specify where clause to remove some data in Delete command. like:

Delete from Table1 where ID > 20

You can't specify where cluase with Truncate command. Truncate command will remove all the data from table.

2. Transaction:
Delete command is a part of transaction. So you need to commit your transaction to make changes permanent.
Truncate command is not participate in transaction. So you don't required to do anything with transaction.

3. Trigger:
Delete command will fire delete trigger of the table. Truncate command will not fire any trigger.

4. Log:
SQL server logs information regarding delete operation. So everytime log file increased when you will execute delete command. SQL server do not log any information regarding Truncate operation. So Truncate operation is faster than Delete operation.

5. Identity Value:
Truncate reset identity value if table have any identity column. Delete operation not reset any identity value.