Difference between Delete, Truncate and Drop command in SQL

This is one of the common SQL interview questions. Moreover, these commands are used more often in our day to day work. So let us go ahead and learn the purpose of each command. Hopefully, this short article will be beneficial for you. 

Before we understand the commands, there is an important concept which we need to understand first:

 

What is Data and Schema in SQL Table?

A table is created using the Create command and it specifies the structure / skeleton of the table. It specifies the name of the table, no. of columns a table will have, the data type of each column etc. Whereas the Data is the actual values which get inserted as a row for each of the table columns.

 

Let us understand the main question now:

 

1) Delete:

Definition: Delete command is used to delete existing records / rows from the table.

Syntax: Delete From SchemaName.TableName where ColumnName = filterValue; 

Important Points:

• Delete command only deletes the data, table schema is not deleted.
• Where clause can be applied to delete specific rows.
• The Delete command maintains the Transaction log so that if a transaction fails in the middle, it can be rolled back successfully to its original state. This behaviour makes Delete slightly slower than Truncate.
• It is DML (Data Manipulation Language) Command.
• It reports the count of deleted rows in the output pane.

 

2) Truncate:

Definition: Truncate command is used to delete all the records / rows from the table. In other words, It empties the table.

Syntax: Truncate Table SchemaName.TableName;

Important Points:

• Truncate command deletes all the records from the table. Table schema is not deleted.
• Where clause can’t be applied with truncate command
• Truncate does not maintain Transaction log for the deleted records which means roll back can’t be made. In MySQL, the Truncate command drops the table and re-creates it using the create statement. This behaviour makes Truncate fast.
• It is DDL (Data Definition Language) Command.
• Truncate does not report the deleted rows count in the output pane. It simply says “0 rows affected”

 

3) Drop:

Definition: Drop command is used to delete the table schema along with data.

Syntax: Drop Table SchemaName.TableName;

Important Points:

• Drop deletes all the data, moreover deletes the table schema as well. Once drop is applied, you will not see the table in the database.
• It is DDL (Data Definition Language) Command.

 

 

Delete and Truncate, which one to use when?

• When you want to delete all the records from the tables, always go with the Truncate command. It is faster.
• If you want to delete specific records then this can be achieved with Delete command only. Also, delete command makes sure that if the transaction is failed for some reason, then already deleted records will be rolled back to its original state.