Difference Between Delete, Drop, Truncate | SQL Server

Today we will learn the difference between Delete, Drop, and Truncate commands.

DELETE:

Delete is a DML command and is used to remove tuples/rows/records from a table (the structure of the table exists in the database, and only records remove from the table). We can remove one or multiple rows from a table by using the where clause because Delete is a DML (Data Manipulation Language) command so, it can be ROLLBACK as well.

Example:-

Delete from person	It will delete all rows/records from a table named person
Delete from person where name='Amir'	It will delete all those rows/records where the name filed contains Amir.

DROP:

Drop command is used to remove/drop table from the database (the structure of the table will be removed with records as well). Drop command cannot be ROLLBACK.

Example:-

Drop table person.

TRUNCATE:

Truncate command also remove/delete rows/records from a database table (structure will exist in the database and only records will be removed). It is much faster than a DELETE command. Also, this command cannot be ROLLBACK.

Example:-

Truncate table person.

Note: - Truncate and Drop are DDL commands but Delete is a DML command. That is the reason Delete operation can be ROLLBACK but Drop and Truncate cannot.