Difference between DROP and TRUNCATE in SQL

Examples of DROP Statements

Before understanding the difference between drop and truncate statements, use an example to understand the DROP statement thoroughly. Here is an example of a DROP Statement.

Create a Table

First, we will create the Ninja table with columns NInja_ID, Ninja_Name, Course, and Duration.

CREATE TABLE Ninja (
    Ninja_ID NUMBER PRIMARY KEY,
    Ninja_name VARCHAR2(50) NOT NULL,
    Course VARCHAR2(50) NOT NULL,
    Duration NUMBER NOT NULL
);

Create Index

Here we are creating an index for the age column.

CREATE INDEX n_Course ON Ninja(Course);

Insert Values

Here, we insert values in our Ninja table.

INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (1, 'Ninja A', 'CN', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (2, 'Ninja B', 'OOPs', '12 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (3, 'Ninja C', 'DBMS', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (4, 'Ninja D', 'CN', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (5, 'Ninja E', 'DSA', '12 weeks');

 

We will get the following table after inserting the values:

Dropping Column and Index

For dropping the Index:

DROP INDEX n_Course;

 

Output:

Index dropped.

 

For deleting the Column Course:

ALTER TABLE Ninja
DROP COLUMN Course;

 

Output:

For dropping the complete Table:

DROP TABLE Ninja;

 

Output:

Table dropped.

What are TRUNCATE Statements?

Before discussing the difference between drop and truncate commands, let’s discuss the truncate statement first.

In the database, TRUNCATE removes the rows and records from a table. Use TRUNCATE TABLE to eliminate every row from a large table while maintaining the table's structure. 

The Syntax of the TRUNCATE statement is as follows:

TRUNCATE TABLE <table_name>

Example of TRUNCATE Statement

Here is an example of a TRUNCATE Statement. We will use the same table to understand the difference between drop and truncate statements.

Create a Table

First, we will create the Ninja table with columns NInja_ID, Ninja_Name, Course, and Duration.

CREATE TABLE Ninja (
    Ninja_ID NUMBER PRIMARY KEY,
    Ninja_name VARCHAR2(50) NOT NULL,
    Course VARCHAR2(50) NOT NULL,
    Duration NUMBER NOT NULL
);

Insert Values

Here we are inserting values in our Ninja table.

INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (1, 'Ninja A', 'CN', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (2, 'Ninja B', 'OOPs', '12 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (3, 'Ninja C', 'DBMS', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (4, 'Ninja D', 'CN', '8 weeks');
INSERT INTO Ninja (Ninja_ID, Ninja_name, Course, Duration) VALUES (5, 'Ninja E', 'DSA', '12 weeks');

 

We will get the following table after inserting the values:

Truncate table

Use the following command to truncate the table.

TRUNCATE TABLE Ninja;

 

Output:

Table truncated.

Difference between DROP and TRUNCATE in SQL

Here is the comparison table to understand the difference between DROP and TRUNCATE statements.

Key Points	DROP	TRUNCATE
Feature	To delete the existing table definition and content in databases.	It is used to delete all the table rows in the database.
Table View	There is no table view for the DROP command.	A table view exists for the TRUNCATE command.
Fast	It is slower than the TRUNCATE command.	It is faster than the DROP command.
Undo Tablespaces	Undo Tablespaces cannot be used.	Undo Tablespaces can be used.
Memory	Table space is free from memory in this command.	Table space is not free from memory in this command.
Integrity	DROP command removes the integrity constraint.	Truncate command maintains the integrity constraints.

Frequently Asked Questions

What is the use of DROP Statements?

The Drop statements are used to delete the existing table in the database. You can also delete an entire database using the DROP Statement. It deletes a table definition and any associated data, triggers, indexes, constraints, and access requirements.

What is the use of TRUNCATE Statements?

An existing table's contents can be deleted using the SQL TRUNCATE statement. You wouldn't need to create that table again if you wanted to store some data because it wouldn't remove the entire table structure from the database.

What are DDL Statements in SQL?

DDL stands for Data Definition Language. It describes the database's structure or schema. It is a set of SQL commands that can create, delete, and modify the tables and schemas of the databases.

Which is faster, DROP or TRUNCATE statements in SQL?

The truncate statements in SQL are faster than the drop statements. The main reason behind the fast working of the truncate command is that they delete all the records from the tables without any transaction log creation.

Conclusion

In this article, we explored the Difference between DROP and TRUNCATE in SQL. Both DROP and TRUNCATE statements are DDL commands used for different purposes.

We hope this article helps you. You can visit more articles related to SQL Commands.

• Truncate table command.
   
• TCL Commands in SQL
   
• Basic SQL Commands and Queries.
   
• Alter Vs Update Commands.
   

Read More, sql functions

If you liked our article, do upvote our article and help other ninjas grow.  You can refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Data Structures and Algorithms, Competitive Programming, System Design, and many more!

Head over to our practice platform Coding Ninjas Studio to practise top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!!

Happy Reading!!