Difference between delete and truncate in sql server :
Truncate and Delete both are used to delete data from the table. Please go over through the below difference in DELETE and TRUNCATE.
| DELETE | TRUNCATE |
| The DELETE command is used to remove rows from a table | TRUNCATE removes all rows from a table |
| DELETE is a DML (data manipulation language) command | TRUNCATE is a DDL (data definition language) command |
| You can use WHERE clause with DELETE | You can’t use WHERE clause with TRUNCATE |
| Triggers get fired in DELETE command | A trigger doesn’t get fired in case of TRUNCATE |
| DELETE doesn’t reset identity value | TRUNCATE reset the identity column value |
| You can roll back delete command | You cannot roll back the TRUNCATE command |
| Syntax : DELETE FROM table_name [WHERE CONDITION] |
Syntax :TRUNCATE TABLE table_name |
|
Delete statement removes rows of a table one by one. |
Truncate removes all rows by deallocating the data pages assigned to the table & only these deallocation are recorded in the transaction log. |
| Slower | Faster |
| Delete keep the lock over each row | Truncate keeps the lock on table not on all the row |