Difference between DELETE and TRUNCATE commands in SQL

Delete Command removes the row from the table based on the condition that we provide with a WHERE clause. TRUNCATE will actually remove all the rows from a table an there will be no data in the table after we run the truncate command.

Difference between TRUNCATE and DELETE in SQL

TRUNCATE in SQL

• TRUNCATE removes all the rows from a table, but the table structure, its columns,  constrains, indexes and so on remains. The counter used by an identity for new row is reset to the seed for the column.
• TRUNCATE is DDL Command.
• TRUNCATE resets identity of the table.
• TRUNCATE cannot be rolled back unless it is used in TRANSACTION.
• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
• You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

Syntax for TRUNCATE in SQL
TRUNCATE TABLE table_name

DELETE in SQL

DELETE can be used with or without a WHERE clause
DELETE is DML Command.
DELETE doesn't resets identity of the table.
DELETE can be rolled back
DELETE removes rows one at time and records at entry in the transaction log for each deleted row
DELETE  Activates Trigger.

Syntax for DELETE in SQL
DELETE FROM table_name

Truncate and Delete video tutorial