Delete Vs Truncate

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 
4. Delete activates a trigger because the operation are logged individually. 
5. Slower than truncate because, it keeps logs. 
6. Rollback is possible.

7. We can use Delete command in all types of replications

Delete From [schama].[table_name]

Truncate 


1. TRUNCATE is a DDL command. 
2. TRUNCATE TABLE always locks the table and page but not each row. 
3. We cannot use where Condition in Truncate. 
4. It Deletes all the table's data. 
5. TRUNCATE Table cannot activate a trigger.
6.  Faster in performance wise, because it doesn't keep any logs. 
7.  Rollback is also possible. 
8.  It reset all the constraints i.e. IDENTITY.
9.  T-Replication/Merge-Replication not supports TRUNCATE TABLE COMMAND if the Table is published.
10. We can not truncate a table that has a foreign key reference.
11. We can not truncate a table that participates in an indexed view.
12. For Truncate a Table the minimum permission required is ALTER on the table.

Truncate Table [schama].[table_name]