|TRUNCATE is a DDL command||DELETE is a DML command|
|TRUNCATE TABLE always locks the table and page but not each row||DELETE statement is executed using a row lock, each row in the table is locked for deletion|
|Cannot use Where Condition||We can specify filters in where clause|
|It Removes all the data||It deletes specified data if where condition exists.|
|TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.||Delete activates a trigger because the operation are logged individually.|
|Faster in performance wise, because it is minimally logged in transaction log.||Slower than truncate because, it maintain logs for every record|
|Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table||keeps object’s statistics and all allocated space. After a DELETE statement is executed,the table can still contain empty pages.|
|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 lo||The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row|
|If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column||DELETE retain the identity|
|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|