Difference Between DROP and Truncate

In this article, we will learn about the DROP and Truncate command and the differences between them.

What is DROP Command?

DROP is a command of Data Definition Language which removes or deletes the definition indexes, data, constraints, and triggers from the database tables.

In the Structured Query Language, this statement deletes the elements from the relational database management system (RDBMS).

The DROP command is fast in comparison to the TRUNCATE command in SQL. But, its execution time is slow because it has many complications.

We can also use the DROP keyword with the ALTER TABLE statement for deleting one or more columns from the tables.

The syntax for deleting the database is given below:

DROP DATABASE Database_Name;

In this syntax, we have to specify the name of the database which we want to delete from the system.

Note: Be careful when we delete the database because the DROP command deletes all the tables, indexes, and views included in that database.

The syntax for deleting the table is given below:

DROP TABLE Table_Name;

In this syntax, we have to specify the name of the table which we want to delete from the database.

The syntax for deleting the multiple tables in one statement is given below:

DROP TABLE Table_Name1, Table_Name2, ......, Table_NameN;

In this syntax, we can specify multiple tables using a comma.

The syntax for deleting the index is given below:

DROP INDEX INDEX_Name;

In this syntax, we have to specify the name of the index just after the INDEX keyword.

Unlike the DELETE command in SQL, database users cannot roll back the data from the table after using the DROP command.

The DROP command frees the tablespace from the storage because it permanently deletes the table and its content from the system.

Example of DROP Command

Let's create a new table for implementing the DROP query in SQL. The following CREATE TABLE query creates the Employee table with five fields:

CREATE TABLE Employee
(
Employee_ID INT AUTO_INCREMENT PRIMARY KEY,
Emp_Name VARCHAR (50),
Emp_Salary INT NOT NULL,
Emp_CityVarchar (50),
Emp_EmailID VARCHAR (100)
) ;

The following query deletes the created Employee table from the database:

DROP TABLE Employee;

What is TRUNCATE Command?

TRUNCATE is also another command of Data Definition Language. This command removes all the values from the table permanently.

This command cannot delete the particular record because it is not used with the WHERE clause.

Syntax of TRUNCATE command in SQL:

TRUNCATE TABLE Table_Name;

In the above syntax, we have to specify the name of that table whose all records we want to delete from the table.

Example of TRUNCATECommand

Let's create a new table for implementing the TRUNCATE query in SQL. The following CREATE TABLE statement creates the Student_Details table with five columns:

CREATE TABLE Student_Details
(
Student_ID INT NOT NULL,
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT,
Student_Marks INT
);

The following SQL queries insert the record of students into the above table using INSERT INTO statement:

INSERT INTO Student_Details VALUES (101, Anuj, B.tech, 20, 88);
INSERT INTO Student_Details VALUES (102, Raman, MCA, 24, 98);
INSERT INTO Student_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO Student_Details VALUES (107, Vikash, B.tech, 20, 78);
INSERT INTO Student_Details VALUES (111, Monu, MBA, 21, 65);
INSERT INTO Student_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO Student_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO Student_Details VALUES (123, Divya, B.tech, 21, 89);
INSERT INTO Student_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO Student_Details VALUES (130, Nidhi, BBA, 20, 88);

Let's see the record of the above table using the following SELECT statement:

SELECT * FROM Student_Details;

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88

The following query removes all the rows from the above Student_Details table:

TRUNCATE TABLE Student_Details;

The following statement verifies that the table is successfully truncated or not:

SELECT * FROM Student_Details;

Output:

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks

The following table shows the differences between DROP and TRUNCATE command in Structured Query Language:

DROP	TRUNCATE
The DROP command in SQL removes the table definition and its data.	The TRUNCATE command in SQL deletes all data from the table.
This query frees the tablespace from the memory.	The TRUNCATE query does not free the tablespace from the storage.
The view of the table does not exist in the DROP command.	View of the table exists in the Truncate command.
The integrity constraints will be automatically removed from the table in the DROP command	The integrity constraints in this command will not be removed.
In the DROP query, deleted space is not used.	The deleted space is used but less than the DELETE statement.
The DROP query deletes data quickly, but there are so many complications.	The TRUNCATE query in SQL is faster than the DROP query.

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz