Difference between Delete and truncate in sql query

Both the TRUNCATE statement and the DELETE statement are included in the category of SQL queries for deleting the data stored in a table. They carry out deletion operations on records or rows of a table that are no longer needed. A condition is applied before each entry in the table that is being deleted when using the DELETE command. To put it another way, it is possible to delete one or more rows all at once. However, with the TRUNCATE command, each row is removed from the table simultaneously.

When we delete something using the DELETE query, a log entry is produced for each row in the transaction log. Because of this, we are able to retrieve the records by using ROLLBACK before committing the changes. In addition, if we use the TRUNCATE operation, we can retrieve the records by using the ROLLBACK procedure. TRUNCATE, on the other hand, only tracks the deallocation of the page where the data is kept. This is the key difference.

What is a DELETE Command?

DELETE is an example of a DML (Data Manipulation Language) command. It can be used to delete a single row (record) or numerous rows (records) at once from a table.

Within the context of the DELETE command, the WHERE clause denotes a criterion that must be met in order to remove just the undesirable rows from the target table.

A log file is produced in the transaction log whenever the DELETE query is run. It saves the records initially, then later deletes them. Therefore, in the event that we delete one or more crucial rows, we will be able to retrieve them using the ROLLBACK command.

Due to the fact that the DELETE statement is a DML command, we are need to manually commit the changes. It is important to keep in mind that the ROLLBACK must be completed before the COMMIT.

Syntax

DELETE from any_table Where condition;

What is a TRUNCATE Command?

TRUNCATE is a type of DDL (Data Definition Language) command. When working with tables, we use this command to delete all of the records at once.

The TRUNCATE command does not use any conditions in the same way as the WHERE clause does to define conditions. Therefore, we ought to only utilize it in situations where all of the data in the target database is undesirable.

Instead of using row lock, the TRUNCATE command uses table lock to lock both the table and the page before performing the truncate operation. Therefore, all that is logged is the deallocation of the pages that are used to store the entries; as a result, it is far faster than the DELETE statement.

Because TRUNCATE is a DDL command, the commit operation is completed without human intervention. The identification is reverted back to its seed value when the TRUNCATE command is executed.

Syntax

TRUNCATE TABLE any_table_name;

When to Use Delete and Truncate?

Note that the primary responsibility of DELETE and TRUNCATE is the same, which is to remove records from a table. However, the two commands perform slightly different procedures.

The DROP command is the only one that can remove table schema, indices, constraints, and triggers. Neither of these commands can accomplish that.

• Use the TRUNCATE command if you wish to remove all of the records from a table.

• Use the DELETE command in order to remove individual records from a table.

Difference between DELETE and TRUNCATE

The following table highlights the major differences between DELETE and TRUNCATE −

Basis of Comparison	DELETE	TRUNCATE
Definition	The DELETE command in SQL removes one or more rows from a table based on the conditions specified in those rows.	SQL's TRUNCATE command is used to purge a table of all of its rows, regardless of whether or not any conditions are met.
Language	It is a DML(Data Manipulation Language) command,	It is a DDL(Data Definition Language) command.
Commit	It is necessary for us to make a manual COMMIT after making changes to the DELETE command.	When you use the TRUNCATE command, your modifications are committed for you automatically.
Process	It deletes rows one at a time and applies criteria to each deletion.	It purges all of the information in one go.
Condition	The WHERE clause serves as the condition in this case.	The WHERE clause does not serves as the condition in this case.
Lock	All rows are locked after deletion.	It does this by utilizing table lock, which locks the pages so they cannot be deleted.
Log	It makes a record of each and every transaction in the log file.	The only activity that is recorded is the deallocation of the pages on which the data is stored.
Transaction Space	In comparison to the TRUNCATION command, it consumes a greater amount of transaction space.	In comparison to the DELETE command, it makes less use of the transaction space.
Identity	If there is an identity column, the table identity is not reset to the value it had when the table was created.	It returns the table identity to the value it was given as a seed.
Permission	It requires authorization to delete.	It requires table alter permission.
Speed	When it comes to large databases, it is much slower.	It is faster, or more accurately, instant.

Conclusion

Both the SQL DELETE and SQL TRUNCATE commands can be used to remove records from a table. However, the DELETE command employs the WHERE clause to specify rows in a table for deletion action, whereas the TRUNCATE command does not use any clause and deletes rows all at once. This is the primary distinction between the two commands.

Keep in mind that the TRUNCATE command can be undone in the same way that the DELETE command can.