Some of the responses posted by @Muhsinathk & @Ivinraj.S are taken from my blog post.–> DELETE:
1. Removes Some or All rows from a table.
2. A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed.
3. Causes all DELETE triggers on the table to fire.
4. It removes rows row-by-row one at a time and records an entry in the Transaction logs, thus is slower than TRUNCATE.
5. Every deleted row in locked, thus it requires more number of locks and database resources.
6. According to MS BOL, if a table is a Heap or no Clustered index is defined than the row-pages emptied are not de-allocated instantly and remain allocated in the heap. Thus, no other object can reuse this associated space. Thus to de-allocate the space a Clustered index is required or TABLOCK hint should be applied in the DELETE statement.
7. This is a DML command as it is just used to manipulate/modify the table data. It does not change any property of a table. –> TRUNCATE:
1. Removes All rows from a table.
2. Does not require a WHERE clause, so you can not filter rows while Truncating.
3. With SQL Server 2016 you can Truncate a Table Partition, for more details check [here].
4. IDENTITY columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
5. No Triggers are fired on this operation because it does not operate on individual rows.
6. It de-allocates Data Pages instead of Rows and records Data Pages instead of Rows in Transaction logs, thus is faster than DELETE.
7. While de-allocating Pages it locks Pages and not Rows, thus it requires less number of locks and few resources.
8. TRUNCATE is not possible when a table:
a. is reference by a Foreign Key or tables used in replication or with Indexed views.
b. participates in an Indexed/Materialized View.
c. published by using Transactional/Merge replication.
9. This is a DDL command as it resets IDENTITY columns, de-allocates Data Pages and empty them for use of other objects in the database.
Note: It is a misconception among some people that TRUNCATE cannot be roll-backed. But in reality both DELETE and TRUNCATE operations can be COMMITTED AND ROLL-BACKED if provided inside a Transaction. The only method to Rollback a committed transaction after DELETE/TRUNCATE is to restore the last backup and run transactions logs till the time when DELETE/TRUNCATE is about to happen.
Reference (my blog post): http://sqlwithmanoj.com/2009/02/22/difference-between-truncate-delete-and-drop-commands/ Sqldev, if this helps please login to Mark As Answer. | Alert Moderator