Difference between truncate and delete in MS-SQL Server
Below are the differences between Truncate and Delete in MS-SQL.
|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.|