Search This Blog

Thursday, October 11, 2012

Difference between Delete and Truncate commands in SQL Server

Delete Truncate
DELETE is a DML command TRUNCATE is a DDL command
We can delete all records of the table or only specific records based on filter criteria Truncate deletes all records of the table and there is no provision to delete specific records
A log is maintained in case of delete and hence it is possible to roll back the transaction No log is maintained in case of truncate and hence it is not possible to roll back the transaction
We can execute a trigger in case of delete A trigger cannot be executed in case of truncate
Indexes like identity are not initialized in case of Delete Indexes like identity are initialized in case of Truncate
Example: Delete from where Example: Truncate table