1.delete command support where clause and it can delete only specific rows or all rows from the table.
-Truncate command does not support where clause and it can only delete all rows from the table.
2.delete command deletes rows one by on
-Truncate command deletes rows page by page
Truncate reset identity while delete do not reset identity
1-we can use where clause in delete but can't do in truncate.
2-we can use trigger in delete but can't do in truncate.
3-delete is slower than truncate
4-delete makes log files while truncate can't do that so truncate is much faster than delete.
5- we can use delete in triggers but not in truncate.
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
Delete Never removes Identity, But Truncate removes identity.
If the Foreign Key represented means delete possible. But Truncate not Possible when it has the references...
Delete command delete single row or multiple rows are all rows and these rows can be rolled back(we can un delete)
Truncate command is used to delete all the rows in table except table structure(the rows deleted using truncate command cannot be un deleted). This is an autocommit command
in truncate there in no possible for individual record deletion we can delete all records at a time but by using delete possible
As they both remove rows from a table, Truncate will also 'reset' any identity seeds as though none have been set. I'd recommend you only use Truncate when running tests before production unless you really know what you're wanting to do. Also, Truncate is usually only available as a T-SQL command and not something you can access via EF or LINQ or any other method besides inline string SQL...
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.