DROP and TRUNCATE in SQL

DROP and TRUNCATE in SQL remove data from the table. The main difference between DROP and TRUNCATE commands in SQL is that DROP removes the table or database completely, while TRUNCATE only removes the data, preserving the table structure.

Let’s understand both these SQL commands in detail below:

What is DROP Command?

DROP command in SQL is used to delete a whole database or a table.

DROP statement deletes objects like an existing database, table, index, or view.

Syntax

DROP object object_name ;

DROP Command Examples

Let’s look at some examples of the DROP statement in SQL.

DROP Table

DROP TABLE table_name;

table_name: Name of the table to be deleted.

DROP database

Syntax:

DROP DATABASE database_name;

database_name: Name of the database to be deleted.

What is TRUNCATE Command?

TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extent of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing several integrity-enforcing mechanisms.

It was officially introduced in the SQL:2008 standard. The TRUNCATE TABLE statement is logically (though not physically) equivalent to the DELETE Statement without a WHERE clause.

Syntax

TRUNCATE TABLE  table_name;

Where,

• table_name: Name of the table to be truncated.
• DATABASE name: student_data

Differences Between DROP and TRUNCATE

The key differences between DROP and TRUNCATE statements are explained in the following table:

DROP	TRUNCATE
In the drop table data and its definition is deleted with their full structure.	It preserves the structure of the table for further use exist but deletes all the data.
Drop is used to eliminate existing complications and fewer complications in the whole database from the table.	Truncate is used to eliminate the tuples from the table.
Integrity constraints get removed in the DROP command.	Integrity constraint doesn’t get removed in the Truncate command.
Since the structure does not exist, the View of the table does not exist in the Drop  command.	Since the structure exists, the View of the table exists in the Truncate command.
Drop query frees the table space complications from memory.	This query does not free the table space from memory.
It is slow as there are so many complications compared to the TRUNCATE command.	It is fast as compared to the DROP command as there are fewer complications.

Read More: Differences between DROP and TRUNCATE Commands

SQL DROP AND TRUNCATE Statement Examples

Let’s look at some examples of the DROP and TRUNCATE statements in SQL and understand their working:

let’s consider the given database Student_data: 

Student Table

To create this table, write the following queries:

SQL

CREATE TABLE Student_details (
ROLL_NO INT,
NAME VARCHAR(25),
ADDRESS VARCHAR(25),
PHONE INT ,
AGE INT); --Inserting the data in Student Table
INSERT INTO Student_details(ROLL_NO,NAME,ADDRESS,PHONE,AGE) VALUES 
(1,'Ram','Delhi',9415536635,24),
(2,'Ramesh','Gurgaon',9414576635,21),
(3,'Sujit','Delhi',9815532635,20),
(4,'Suresh','Noida',9115536695,21),
(5,'Kajal','Gurgaon',8915536735,28),
(6,'Garima','Rohtak',7015535635,23);

We will perform the examples on this particular table.

DROP Database Example

In this example, we will drop the student_data database.

Query:

DROP DATABASE student_data; 

After running the above query whole database will be deleted.

DROP Table Example

In this example, we will drop the student_details table;

Query:

DROP TABLE student_details; 

After running the above query whole table from the database will be deleted.

TRUNCATE Table Example

In this example, we will truncate the Student_details table from the student_data database.

Query:

TRUNCATE TABLE Student_details;

After running the above query Student_details table will be truncated, i.e, the data will be deleted but the structure will remain in the memory for further operations.

Important Points About SQL DROP & TRUNCATE Statements

SQL DROP Statement

• Completely removes a table or database from the database, including the data and structure.
• Is a permanent operation and cannot be rolled back.
• Removes integrity constraints and indexes associated with the table.
• Is slower compared to the TRUNCATE statement.

SQL TRUNCATE Statement

• Removes all the rows or data from a table, but preserves the table structure and columns.
• Is a faster operation compared to the DROP statement.
• Resets the identity column (if any) back to its seed value.
• Does not remove integrity constraints associated with the table.
• Can be rolled back, unlike the DROP statement.