WHAT IS DIFFERENCE BETWEEN DELETE AND TRUNCATE in SQL?

DELETE VS TRUNCATE

delete vs truncate commands in sql
Sr.DELETETRUNCATE
1DELETE is a DML commandTRUNCATE is a DDL command
2Remove rows or specific records from a table based on a WHERE conditionRemoves all rows from a table, can’t use WHERE condition
3To use DELETE you need delete permission on the tableTo use TRUNCATE on a table you need at least ALTER permission on the table.
4DELETE executed using row lockTRUNCATE executed using table lock
5DELETE retains the identity of the column valueIdentity column is reset to its seed value if the table contains any identity column
6DELETE can be used with indexed viewsTRUNCATE cannot be used with indexed views.
7Delete removes rows one at a time and records an entry in the transaction log for each deleted rowTRUNCATE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
8DELETE activates a trigger because the operation is logged individuallyTRUNCATE can’t activate a trigger because the operation does not log individual row deletions
9DELETE Uses more transaction spaceTRUNCATE Uses less transaction space than DELETE statement
DELETE VS TRUNCATE IN SQL

Leave a Reply

Your email address will not be published. Required fields are marked *