Difference between DELETE and TRUNCATE in SQL Server
Let’s discuss this point “Truncate vs Delete” below in simple way.
- Delete is a DML statement whereas Truncate is a DDL statement.
- Delete commmand deletes each row from the table which are satisfying the WHERE clause and logs it in the log file where as Truncate command deallocates the data pages and logs it in the log file.
- Delete performs slower than truncate because of logging mechanism it has whereas Truncate is faster than delete.
- Delete can remove full or partial data as per it’s filter defined in WHERE clause whereas Truncate does not support WHERE clause and all data will be removed from table.
- Delete executes the Triggers on the table if it has any because it is a DML command whereas Truncate does not execute the Triggers.
- Delete requires more locks than truncate because each row is locked for deletion