What are the key differences between the DELETE and TRUNCATE commands in T-SQL?

Key Differences between DELETE and TRUNCATE Commands in T-SQL

DELETE Command:

• Used to remove specific rows from a table based on a condition.
• Can be rolled back using a transaction if required.
• Deletes rows one at a time, which can be a slower process for large tables.
• Triggers are fired for each deleted row.
• Does not reset the identity seed value of the table.

TRUNCATE Command:

• Removes all rows from a table without specifying any condition.
• Cannot be rolled back using a transaction.
• Deallocates the data pages used by the table, making it faster than DELETE for large tables.
• Triggers are not fired for the rows deleted by TRUNCATE.
• Resets the identity seed value of the table to its initial value.

Overall, DELETE is more flexible and allows for conditional removal of rows, while TRUNCATE is faster and more efficient for bulk deletion of data.