Difference Between TRUNCATE, DELETE and DROP In SQL Server

Hi All,

Hope you all are doing good.

In this article, we will know about what are the various differences between TRUNCATE, DELETE and DROP statements in SQL server.

TRUNCATE	DELETE	DROP
DDL Command	DML Command	DDL Command
It locks whole table to remove its records	It locks each row in table to remove its records	The DROP command removes a table from the database.
We cannot use WHERE clause with Truncate	We can use WHERE clause to delete specified records of table.	All the table rows, indexes and privileges will also be removed.
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.	No DML triggers will be fired.
Minimal logging in transaction log, so it is faster performance wise.	It maintains log, so it is bit slow in performance wise.
Truncate cannot be used with indexed views.	Delete can be used with indexed views.
This operation cannot be rolled back	This operation can be rolled back	This operation cannot be rolled back

Thanks for reading my article.