Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Difference between DELETE and TRUNCATE

  • Difficulty Level : Easy
  • Last Updated : 19 Jul, 2021

1. DELETE : 
DELETE is a DML(Data Manipulation Language) command and is used when we specify the row(tuple) that we want to remove or delete from the table or relation. The DELETE command can contain a WHERE clause. If WHERE clause is used with DELETE command then it remove or delete only those rows(tuple) that satisfy the condition otherwise by default it removes all the tuples(rows) from the table. 

Syntax of DELETE command : 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

DELETE FROM TableName 
WHERE condition; 

2. TRUNCATE : 
TRUNCATE is a DDL(Data Definition Language) command and is used to delete all the rows or tuples from a table. Unlike the DELETE command, TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is recorded. Unlike the DELETE command, the TRUNCATE command is fast. We cannot rollback the data after using the TRUNCATE command. 

Syntax of TRUNCATE command:- 

TRUNCATE TABLE  TableName; 

Let’s see the difference between DELETE and TRUNCATE command:- 

 

S.NODeleteTruncate
1.The DELETE command is used to delete specified rows(one or more).While this command is used to delete all the rows from a table.
2.It is a DML(Data Manipulation Language) command.While it is a DDL(Data Definition Language) command.
3.There may be WHERE clause in DELETE command in order to filter the records.While there may not be WHERE clause in TRUNCATE command.
4.In the DELETE command, a tuple is locked before removing it.While in this command, data page is locked before removing the table data.
5.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.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.
6.DELETE command is slower than TRUNCATE command.While TRUNCATE command is faster than DELETE command.
7.To use Delete you need DELETE permission on the table.To use Truncate on a table we need at least ALTER permission on the table.
8.Identity of column retains the identity after using DELETE Statement on table.Identity of the column is reset to its seed value if the table contains an identity column.
9.The delete can be used with indexed views.Truncate cannot be used with indexed views.
My Personal Notes arrow_drop_up
Recommended Articles
Page :