Drop vs Truncate in SQL

In this article, we will learn and understand the Drop and Truncate commands and the difference between these two commands.

What is Drop Command?

Drop is a Data Definition Language command in the Structured Query Language. The Drop command in SQL is used to delete or drop the table definition, the database, indexes, view, triggers, and data constraints from the database tables.

 In the Structured Query Language, the drop command is used to drop the elements from the relational database management system (RDBMS).

The Delete command can roll back data once it is performed, but the Drop commands cannot roll back data once it is performed. The Drop command is used to free memory where table space is stored because drop commands delete the table and its contents permanently.

The Drop command is fast as compared to the truncate command in the Structured Query Language. But, the execution timing of the drop command is slow because it has many complications. 

To delete one or more columns from the table, we use the drop command with the ALTER TABLE command.

The syntax for DROP command to remove the database is as follow:

DROP DATABASE Database_Name;

In the above syntax, we have to specify the database name that we want to delete from the system.

Note: - Be careful before using the DROP command for deleting the database because the drop commands remove all the objects from the database like tables, indexes, views, triggers created in the database.

The syntax for removing the table is as follow:

DROP TABLE Table_Name;

In the above syntax, we have to specify the table name that we want to delete from the database.

The syntax for removing the multiple tables from the same database in a single statement is as follow:

DROP TABLE table1, table2, table3;

We can delete multiple tables from the same database using the above syntax.

The syntax for removing the index is as follow:

DROP INDEX Index_Name;

We have to specify the index name in the above syntax just after the Index Keyword.

The syntax for removing the view is as follow:

DROP VIEW View_Name;

We have to specify the view name in the above syntax just after the View Keyword.

The syntax for removing the constraint from the table using the ALTER TABLE is as follow:

ALTER TABLE Table_Name DROP CONSTRAINT Constraint_Name;

Using the ALTER TABLE command in the SQL, we can delete the constraint from the table.

Example of DROP command:

We will take a few examples using the DROP command.

Step 1: Create a database or use an existing database. We will use two databases for the DROP command.

Step 2: Create a new table or use an existing table, index, view.

Example 1: Write a query to drop table students_information from the school database.

We will first select the School database using the USE Keyword followed by the database name.

USE School;

We will write the drop command to delete the students_information table from the selected database.

Consider the table Students_information with the following records:

Student_Id	Student_Name	Student_Course	Student_Marks
1	Anjali	BCOM	85
2	Pranav	BCA	80
3	Yogesh	B.E	88
4	Bhushan	MBA	95
5	Poonam	MCOM	97
6	Bhavesh	B.E	90
7	Khushi	BSC	94
8	Piyush	BCOM	75
9	Nikita	BA	88
10	Aishwariya	BSC	70

DROP TABLE Students_Information;

We have dropped the Students_information table with all the records present inside the table from the above query.

To cross check whether the query was executed successfully or not, we will execute the SELECT query.

SELECT * FROM Students_Information;

As we execute the select query on the Students_information table, the table doesn't exist message is displayed, which means the Drop command query on the Students_information table is executed successfully.

Example 2: Write a query to drop manager_view1 view from the company database.

Consider the manager_view1 view with the following records:

MANAGERID	MANAGER_NAME	MANAGER_DEPARTMENT
1	Snehdeep Kaur	ORACLE
3	Abhishek Manish	JAVA

We will write the drop command to delete the manager_view1 view from the selected database.

DROP VIEW Manager_view1;

We have dropped the manager_view1 view from the above query with all the records present inside the view.

To cross_check whether the query was executed successfully or not, we will execute the SELECT query.

SELECT * FROM Manager_view1;

As we execute the select query on the manager_view1 view, the view doesn't exist message is displayed, which means the Drop command query on the manager_view1 view is executed successfully.

Example 3: Write a query to drop the Cricket database from the system.

Before executing the drop command for the database, we first execute the show databases command to verify at the end of the drop command that we successfully dropped the Cricket database from the system.

SHOW DATABASES;

Database

Chk

Company

Cricket

Employee

Information_schema

Mysql

Performance_schema

Phpmyadmin

School

Test

WordPress

Above are the databases that already exist in the system; we will drop the Cricket database from the system. Dropping the database means deleting all the objects present inside the database table, view, index, triggers, etc.

Now, we will execute a query for dropping the Cricket database.

DROP DATABASE Cricket;

We have executed the drop query on the Cricket database. To check if the database is dropped successfully or not, we will execute the SHOW DATABASES query.

SHOW DATABASES;

Database

Chk

Company

Employee

Information_schema

Mysql

Performance_schema

Phpmyadmin

School

Test

WordPress

As you can see, the cricket database is dropped from the system successfully.

Example 4: Write a query to delete the last name column from the employee table.

ALTER TABLE employee DROP Last_Name;

We have deleted the Last_Name column from the employee table using the DROP command with the ALTER command in the above query.

Field	Type	Null	Key	Default	Extra
EMPLOYEEID	int(11)	NO	PRI	NULL
FIRST_NAME	varchar(20)	YES		NULL
SALARY	int(11)	YES		NULL
CITY	varchar(20)	YES		NULL
DEPARTMENT	varchar(20)	YES		NULL
MANAGERID	int(11)	YES		NULL

The above output shows that Last_Name is deleted from the employee table.

What is Truncate Command?

Truncate is another Data Definition Language command in the Structured Query Language. The Truncate command is used to delete all the records from the table. Like the DROP command, but the TRUNCATE command does not contain a WHERE clause. The Truncate command is faster than both the DROP and the DELETE command. We cannot roll back the records like the DROP command after using the TRUNCATE command.

Syntax of TRUNCATE command in SQL:

TRUNCATE TABLE tablename;

In the above syntax, we have to mention the table name whose data we want to delete from the table.

We will take a few examples using the TRUNCATE command.

Example of TRUNCATE command:

Step 1: Create a database or use an existing database.

Step 2: Create a new table or use an existing table.

Consider the existing table with the following records:

Table: Employee:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4

Example: Write a query to delete all the records from the employee table using the truncate command.

TRUNCATE TABLE Employee;

We have deleted all the records from the above query from the employee table.

To cross check whether all the records from the employee table are deleted or not successfully, we will execute the SELECT query on the employee table.

SELECT * FROM Employee;

EMPLOYEEID

FIRST_NAME

LAST_NAME

SALARY

CITY

DEPARTMENT

MANAGERID

As we execute the select query on the employee table, the empty set message is displayed, which means the truncate command on the employee table is executed successfully.

The below points show the difference between the Drop command and the Truncate command in the Structured Query Language:

The DROP Command	The TRUNCATE Command
The DROP command deletes the table structure and table records.	The TRUNCATE command drops all records from the table.
The DROP command is a Data Definition Language command.	The TRUNCATE command is also a Data Definition Language command.
In the DROP command, table space is free from memory	The TRUNCATE command does not free the table space from the memory.
The table view does not exist in the DROP command	The table view exists in the TRUNCATE command
In the DROP command, we cannot use delete space.	In the TRUNCATE command, we can use delete space but less than compared to the DELETE statement.
In the DROP command, the integrity constraints will be removed automatically from the table.	The integrity constraints will not be removed from the table in the TRUNCATE command.
The DROP command deletes the records fast, but there are many complications.	The TRUNCATE command is faster than the DROP command.