Delete 1.DELETE is a DML command. 2.DELETE statement is executed using a row lock, each row in the table is locked for deletion. 3.We can specify filters in where clause.It deletes specified data if where condition exists.
4.Delete activates a trigger because the operation are logged individually. 5.Slower than truncate because, it keeps logs 6.DELETE retain the identity value of column. 7.After a DELETE statement is executed, the table can still contain empty pages. 8.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. 9.DELETE statement can be used even if you have Replication/CDC enabled for the table. 11.You can use DELETE statement on a parent table and if CASCADE ON DELETE is enabled then data from child table also get deleted. If CASCADE ON DELETE is NOT enabled and any of the child table has related then you can’t delete records from parent table.
Truncate 1.TRUNCATE is a DDL command. 2.TRUNCATE TABLE always locks the table and page but not each row. 3.Cannot use Where Condition. 4.TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. 5.Faster in performance wise, because it doesn’t keep any logs. 6.If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column. 7.TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log. 8.In truncate table the counter used by an identity column for new rows is reset to the seed for the column. 9.TRUNCATE statement can NOT be used if you have Replication/ CDC enabled for the table. 10.You can’t truncate a parent table irrespective of whether the child table has any record or not. Truncate table statement will fail for parent table even if CASCADE ON DELETE is enabled.Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication. Delete works at row level, thus row level constrains apply