Below are the differences between Truncate and Delete in MS-SQL.
| Delete || Truncate |
| Can apply WHERE condition to filter records that need to be deleted. || Cannot apply WHERE condition. |
| Delete is DML command. || Truncate is DDL command. |
| Works against table row (locks the table row). || Works against table (locks the table). |
| By using transactions you can rollback the deleted records. || You cannot roll back the deleted records. |
| Trigger will fire when Delete the rows. || Truncate cannot activate Trigger (Trigger will no fire) |
| No impact on identity. || Resets the Column identity. |