What is the difference between the “DELETE” and “TRUNCATE” commands?

SQL enables users to work with databases in a variety of ways, including querying data, adding, updating, and removing records;
 designing and editing database schemas (tables, views, indexes, etc.); and managing permissions for database access.In this artcle we have delete and truncate keyword is most common and usefull phase.

Definition of TRUNCATE command :

"The TRUNCATE command can be used to  remove every row from a table and free up the table's space without erasing the table's structure. "

Syntax

-- Truncate all rows from the "developer" table
TRUNCATE TABLE developer;

Definition of Delete command :

"DELETE command is used to remove one or more rows from a table based on a specified condition. Here is the basic syntax of the DELETE command".

Syntax

-- Truncate one or more rows from the "developer" table

DELETE FROM developer
WHERE condition;

Below is point of difference between delete and truncate :

1. The DELETE command is used to remove rows from a table based on a WHERE condition whereas TRUNCATE removes all rows from a table.

2. So we can use a where clause with DELETE to filter and delete specific records whereas we cannot use a Where clause with TRUNCATE.

3. DELETE is executed using a row lock, each row in the table is locked for deletion whereas TRUNCATE is executed using a table lock and the entire table is locked for removal of all records.

4. DELETE is a DML command whereas TRUNCATE is a DDL command.

5. DELETE retains the identity of the column value whereas in TRUNCATE, the Identify column is reset to its seed value if the table contains any identity column.

6. To use Delete you need DELETE permission on the table whereas to use Truncate on a table you need at least ALTER permission on the table.

7. DELETE uses more transaction space than the TRUNCATE statement whereas Truncate uses less transaction space than DELETE statement.

8. DELETE can be used with indexed views whereas TRUNCATE cannot be used with indexed views.

9. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row whereas TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

10. Delete activates a trigger because the operation is logged individually whereas TRUNCATE TABLE can't activate a trigger because the operation does not log.

Similar SQL FAQ Question 

1. What is the difference between the "DELETE" and "TRUNCATE" commands?.
2. what is the difference between the delete and truncate commands in sql?
3. what is the difference between delete drop and truncate commands?
4. describe the difference between the delete and truncate commands.
5. what is the difference between drop truncate and delete sql commands ?

Conclusion:

In this article ,We see that while TRUNCATE is more efficient for removing all rows from a table.
when using the DELETE statement, especially without a WHERE clause,
 as it can lead to the loss of data. It's often a good practice to take a backup before performing such operations, especially in a production environment.
We provided SQL  Interview Question for learning.