What is DELETE in SQL?
It's a data manipulation language (DML) command that deletes records from a table that is no longer needed in the database. It deletes the entire row from the table and returns the number of rows that have been deleted. It specifies that we have a database backup before running this operation, as this query will not be able to restore deleted records.
As a result, database backups enable us to restore data at any time in the future. A WHERE clause can be included in the DELETE command. When the WHERE clause is used with the DELETE command, only those rows(tuples) that satisfy the criterion are removed or deleted; otherwise, all tuples(rows) are removed by default.
What is TRUNCATE in SQL?
In SQL, the TRUNCATE statement is a DDL (data definition language) command that removes all of the data from a table while leaving the structure intact. Because we can't utilize 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, unlike the DELETE command, does not have a WHERE clause. The transaction log for each destroyed data page is not recorded when using the TRUNCATE command. The TRUNCATE command is faster than the DELETE command. After invoking the TRUNCATE command, we are unable to reverse the data.
What is the Difference Between DELETE and TRUNCATE in SQL?
We have provided the difference between DELETE and TRUNCATE commands listed in the table below.
Difference Between DELETE and TRUNCATE in SQL
|When we wish to remove some or all of the records from a table, we use the DELETE statement.||TRUNCATE is a SQL statement that deletes whole rows from a table.|
|DELETE is a DML command because it just changes the data in the table.||TRUNCATE is a DDL command.|
|It can be used with the WHERE clause.||It cannot be used with the WHERE clause.|
|It is slower.||It is faster.|
Before deleting a table row, it locks it.
|It locks the whole table.|
|It allows us to restore the deleted data using the COMMIT or ROLLBACK statement.||After executing this command, we cannot restore the deleted data.|