Differences between TRUNCATE and DELETE commands in SQL

In my previous post I explained what SQL, DDL, DML,TCL and DCL. So here I am going to explain only diffreneces between TRUNCATE and DELETE commands.

SQL Commands:

SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

Types of Commands:-

1.  DDL
2.  DML.
3.  TCL.
4.  DCL.


Differences between TRUNCATE and DELETE

Sl. No.	TRUNCATE	DELETE
1.	TRUNCATE is a DDL command	DELETE is a DML command
2.	TRUNCATE is executed using a table lock and whole table is locked for remove all records.	DELETE is executed using a row lock, each row in the table is locked for deletion.
3.	We cannot use Where clause with TRUNCATE.	We can use where clause with DELETE to filter & delete specific records.
4.	TRUNCATE removes all rows from a table.	The DELETE command is used to remove rows from a table based on WHERE condition.
5.	Minimal logging in transaction log, so it is performance wise faster.	It maintain the log, so it slower than TRUNCATE.
6.	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.	The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
7.	Identify column is reset to its seed value if table contains any identity column.	Identity of column keep DELETE retain the identity
8.	To use Truncate on a table you need at least ALTER permission on the table.	To use Delete you need DELETE permission on the table.
9.	Truncate uses the less transaction space than Delete statement.	Delete uses the more transaction space than Truncate statement.
10.	Truncate cannot be used with indexed views	Delete can be used with indexed views
11.	Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	Keeps object’s statistics and all allocated space. After a DELETE statement is executed, the table can still contain empty pages.
12.	TRUNCATE TABLE can’t activate a trigger because the operation does not log individual row deletions. When we run truncate command to remove all rows of table then it actually doesn’t removes any row, rather it deallocates the data pages. In case of Truncate triggers will not be fired because no modification takes place, we have just deallocated the data pages not deleted any row from table.	Delete activates a trigger because the operation are logged individually. When we execute Delete command, DELETE trigger will be initiated if present. Delete is a DML command and it deletes the data on row-by-row basis from a table. Which means delete is modifying the data by deleting it from the table. Triggers are fired when a DML statement executed on a table, so trigger will be fired in case of Delete command execution.

Its a very important and very frequently asked question by interviewers. So I hope you enjoyed it.