HomeSQL ServerDifference between DELETE and TRUNCATE command in SQL Server

Difference between DELETE and TRUNCATE command in SQL Server

Below are few points that differentiate the DELETE and TRUNCATE command.

Sl. No.

DELETE

TRUNCATE

1 This removes the rows one at a time and then records it in the transaction log for each of those deleted row. This used fewer transaction log than delete and is faster
2 This can be used with or without a Where clause This removes all rows from a table
3 Can be rolled back. Cannot be rolled back
4 It is a DML Command. It is a DDL Command.
5 When an Identity is used , it is not reset Identity is reset

 

    1 Comment

  1. Albin Sunnanbo
    July 5, 2012
    Reply

    Another major difference is that TRUNCATE does not allow foreign keys on the table, you need to drop and recreate any foreign keys.
    DELETE on the other hand only requires you to delete from tables in the correct order or use cascading deletes.

Leave a Reply

You May Also Like

When dealing with a relational database management system (RDBMS) like SQL Server, compatibility level is an important concept to understand....
In this blog post, let’s learn about the error message “49975 – Unable to load controller client certificate due to...
In this blog post, let’s learn about the error message “49973 – Cannot remove tempdb remote file to local tempdb...