DELETE vs TRUNCATE in SQL Server

Delete command removes the specific set of rows from a table based on the condition in a WHERE clause. Truncate command removes all the rows from a table and there will be no data in the table after we run the truncate command.

DELETE

• DELETE removes rows and records an entry in the transaction log for each deleted row. 
• DELETE does not reset identity counter of the table.
• DELETE can be used with or without a WHERE clause
• DELETE Activates Triggers. 
• DELETE can be rolled back.
• DELETE is DML Command. 

DELETE command syntax:

Use [MorganDB]
GO
Delete EMPLOYEES Where Age<18

TRUNCATE

• TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. 
• TRUNCATE will not support condition based delete.
• TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. 
• TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, and indexes  are remains same.
• Cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. 
• TRUNCATE cannot be rolled back using log files, but you can rollback if you used the Transaction to execute truncate command and it can’t be rolled back after Transaction Session is closed.
• TRUNCATE is DDL Command.
• TRUNCATE Resets identity counter of the table

TRUNCATE command syntax:

Use [MorganDB]
GO
Truncate Table EMPLOYEES 

Thanks,

Morgan

Software Developer