Difference Between Delete and Truncate

Feature	DELETE	TRUNCATE
Purpose	Deletes specified rows (one or more).	Deletes all rows from a table.
Type of Command	DML (Data Manipulation Language) command.	DDL (Data Definition Language) command.
WHERE Clause	Can include a WHERE clause to filter rows for deletion.	Cannot include a WHERE clause.
Locking Mechanism	Locks individual rows (tuples) before deletion.	Locks the data page before removing table data.
Performance	Slower due to row-by-row deletion.	Faster because it deallocates data pages.
Transaction Log	Records an entry in the transaction log for each deleted row.	Records only page deallocation in the transaction log.
Permissions Required	Requires DELETE permission on the table.	Requires ALTER permission on the table.
Identity Column	Does not reset the identity of a column.	Resets the identity column to its seed value if present.
Use with Indexed Views	Can be used with indexed views.	Cannot be used with indexed views.
Triggers	Can activate triggers on the table.	Does not activate triggers.
Transaction Space Usage	Occupies more transaction space.	Occupies less transaction space.

Key Points to Note:

1. DELETE:
• DML Command: This is used for removing specific rows from a table based on the condition provided in the WHERE clause.
• It is slower since it works row by row and logs each row deletion.
• Can be used to delete specific rows, and if necessary, can be rolled back.
• Triggers: DELETE can activate triggers like DELETE, AFTER DELETE, etc.
• You need DELETE permission to use this command.
2. TRUNCATE:
• DDL Command: This is used to remove all rows from a table, and is typically faster than DELETE as it does not log individual row deletions.
• It cannot be rolled back in the same way as DELETE, since it works by deallocating data pages.
• Identity reset: If the table has an identity column, TRUNCATE will reset the column's identity value back to the starting value.
• No Triggers: TRUNCATE does not activate triggers.
• Requires ALTER permission on the table.