What is the difference between DELETE and TRUNCATE statements?

Question

Amit Mohanty · Answer

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 the WHERE clause is used with the DELETE command then it removes or deletes only those rows(tuple) that satisfy the condition otherwise by default it removes all the tuples(rows) from the table. Remember that DELETE logs the row deletions. Syntax: DELETE command DELETE FROM TableName WHERE condition; 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, the TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is not recorded. Unlike the DELETE command, the TRUNCATE command is fast. We cannot roll back the data after using the TRUNCATE command. Syntax: TRUNCATE command TRUNCATE TABLE TableName; For more details check the below links https://www.geeksforgeeks.org/difference-between-delete-and-truncate/ https://www.tutorialspoint.com/difference-between-delete-and-truncate-in-sql-query https://www.mssqltips.com/sqlservertip/4248/differences-between-delete-and-truncate-in-sql-server/

Rajanikant Hawaldar · Answer

https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1

Uday Dodiya · Answer

The TRUNCATE command is used to delete all the rows from the table and free the space containing the table. The DELETE command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table. For More Information Refer Following https://www.geeksforgeeks.org/difference-between-delete-and-truncate/#:~:text=The%20DELETE%20statement%20removes%20rows,deallocations%20in%20the%20transaction%20log.

Brahma Prakash Shukla · Answer

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 the WHERE clause is used with the DELETE command then it removes or deletes only those rows(tuple) that satisfy the condition otherwise by default it removes all the tuples(rows) from the table. Remember that DELETE logs the row deletions. Syntax: DELETE command DELETE FROM TableName WHERE condition; 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, the TRUNCATE command does not contain a WHERE clause. In the TRUNCATE command, the transaction log for each deleted data page is not recorded. Unlike the DELETE command, the TRUNCATE command is fast. We cannot roll back the data after using the TRUNCATE command. Syntax: TRUNCATE command TRUNCATE TABLE TableName;

What is the difference between DELETE and TRUNCATE statements?

Answers (4)