Difference between DELETE and TRUNCATE in SQL Server

DELETE and TRUNCATE are two commands used in SQL Server to remove data from a table, but they have some key differences:

DELETE:

• DELETE is a DML (Data Manipulation Language) command.
• DELETE removes individual rows from a table based on a condition specified in the WHERE clause.
• DELETE operation can be rolled back using the ROLLBACK command.
• DELETE triggers are fired for each deleted row.
• DELETE command is slower compared to TRUNCATE as it maintains logs and can be rolled back.

TRUNCATE:

• TRUNCATE is a DDL (Data Definition Language) command.
• TRUNCATE removes all rows from a table without specifying any condition.
• TRUNCATE operation cannot be rolled back.
• TRUNCATE does not fire delete triggers.
• TRUNCATE command is faster compared to DELETE because it does not maintain logs.

Overall, DELETE is used when specific rows need to be removed from a table, while TRUNCATE is used when all the data in a table needs to be removed quickly and efficiently.