SQL DELETE Vs TRUNCATE Statement

The most prevalent aspect of an interview question is the difference between DELETE and TRUNCATE commands.

They're mostly utilized to get rid of data from a database.

The primary distinction is that the delete statement deletes data without resetting a table's identification.

The truncate command resets the identity of a specific table.

This page describes the distinctions between the DELETE and TRUNCATE commands, which are commonly used interchangeably yet are completely different.

SQL DELETE Statement

The DELETE command is used to delete tuples from a table or relationship. We can delete the entire tuple, including all of its attribute values, from a relation using DELETE.

DELETE does not delete the value of a tuple's specific attribute from the relation.

Using the WHERE clause, you can filter the tuples you want to delete from a table.

If you use the WHERE clause in the DELETE statement, it will only delete the tuples that match the criterion in the WHEREclause.

However, if you don't provide the WHERE clause in the DELETE statement, it deletes or eliminates all tuples from the relation by default.

In a DELETE statement, the WHERE clause can contain nested SELECT-FROM-WHERE statements.

Example: In this example, we have a table called school that currently has 4 records in it. The columns would be id,rno,name and grade.

StudentID	Rno	Name	Grade
1	301	Vibav	A
2	302	Charu	C
3	303	Sree	c
4	306	Shakthi	A

let's work with below query to delete data

DELETE FROM  School WHERE StudentID < 4;

Output:The result will be

StudentID	Rno	Name	Grade
4	306	Shakthi	A

SQL TRUNCATE Statement

TRUNCATE works in a similar way to DELETE in that it deletes tuples from a relation.

It differs in that it removes complete tuples from a relationship. When the TRUNCATE command is run, the table's complete data is destroyed, and each tuple, together with all of its attribute values, is removed from the table.

However, the table structure is still present in the database. As a result, you can re-enter the tuples into the table.

TRUNCATE is a command in the Data Definition Language.

Because we can't utilise the WHERE clause with this operation, we can't filter records.

Because the log is not kept while doing this action, we cannot revert the erased data after running this command.

The TRUNCATE command dealslocates pages rather than rows and creates a transaction log record for the deallocating pages rather than rows.

Because this command locks pages rather than rows, it uses fewer locks and resources. When a table is referenced by a foreign key or participates in an indexed view, we can't utilise the truncate statement.

Example: In this example, we have a table called school that currently has 4 records in it. The columns would be id,rno,name and grade.

StudentID	Rno	Name	Grade
1	301	Vibav	A
2	302	Charu	C
3	303	Sree	c
4	306	Shakthi	A

let's work with below query to truncate table

TRUNCATE FROM  School;

This example would truncate the table called suppliers and remove all records from that table.

SQL Difference Between DELETE And TRUNCATE Statement

There are 15 main distinguish in delete & truncate clause

DELETE	TRUNCATE
When we wish to remove some or all of the records from a table, we use the DELETE statement.	TRUNCATE can also be used to delete tuples or rows from a relation.
When using the DELETE command You have the option of specifying which tuple you want to delete.	When using the TRUNCATE command You won't be able to specify which tuple you want to delete.
DELETE is a Data Manipulation Command because it only changes the data in the tuple.	The TRUNCATE command deletes all rows from a table.
The `WHERE` clause is used by DELETE to filter any specific records/tuples that should be destroyed.	The Data Definition Language is TRUNCATE.
DELETE fires all referential triggers that have been applied to the table.	Because the `TRUNCATE` command does not support the `WHEERE` clause, we are unable to filter rows while truncating.
The DELETE command deletes rows from the table one by one, in the sequence in which they were processed.	Because the TRUNCATE function does not operate on individual rows, no triggers are fired.
The DELETE command necessitates extra locks (row-level locks) and database resources in order to obtain the lock on each deleted row.	The TRUNCATE command acquires the data page lock before destroying it, requiring less locks (table-level lock) and resources.
Each removed row is recorded in the transaction log by the DELETE statement.	TRUNCATE keeps track of each data page's transaction log.
Its performance is slow since it keeps track of transactions.	TRUNCATE is faster than DELETE because it dealslocates data pages rather than rows and records data pages rather than rows in transaction logs.
You must have DELETE permission on the table to use delete.	To use TRUNCATE on a table, it must have at least `ALTER` permission.
Using the COMMIT or ROLLBACK statements, we can restore the erased data.	TRUNCATE is not a ROLLBACK command.
It's compatible with indexed views.	It isn't compatible with indexed views.
Because it keeps a log for each removed row, the DELETE statement takes up more transaction space than truncate.	Because it keeps a transaction log for the entire data page rather than each row, the TRUNCATE statement uses less transaction space.
After invoking the DELETE statement on the table, the column's identity is preserved.	Personality If the table contains an identity column, the column is reset to its seed value.
Because the DELETE command simply deletes data, it does not reset the table identity.	TRUNCATE resets the table identity every time.

All Tutorials