Open In App

DROP and TRUNCATE in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

SQL commands are broadly classified into two types DDL, DML here we will be learning about DDL commands, and in DDL we will be learning about DROP and TRUNCATE in this article.

DDL Stands for Data Definition Language with the help of this DDL command we can add, remove, or modify tables within a database. Here we are to discuss DROP and TRUNCATE Commands So we are going to start with the DROP command first.

What is DROP Command?

DROP is used to delete a whole database or just a table.

In this article, we will be learning about the DROP statement which destroys objects like an existing database, table, index, or view. A DROP statement in SQL removes a component from a relational database management system (RDBMS)

Syntax

DROP object object_name ;

Case 1: To Drop a table

DROP TABLE table_name;

table_name: Name of the table to be deleted.

Case 2: To Drop a database

Syntax:

DROP DATABASE database_name;

database_name: Name of the database to be deleted.

What is TRUNCATE Command?

The major difference between TRUNCATE and DROP is that truncate is used to delete the data inside the table not the whole table.

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 mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).

Syntax

TRUNCATE TABLE  table_name;

table_name: Name of the table to be truncated.

DATABASE name – student_data

 Differences Between DROP and TRUNCATE

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.

let’s consider the given database Student_data: 

Query

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);

Output

Student Table

Student Table

Example1:

To delete the whole database

Query:

DROP DATABASE student_data; 

After running the above query whole database will be deleted.

Example2:

To delete the whole table from the Database

Query:

DROP TABLE student_details; 

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

Example3:

To 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.

Conclusion

  • Truncate is normally ultra-fast and it’s ideal for deleting data from a temporary table.
  • Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
  • Table or Database deletion using a DROP statement cannot be rolled back, so it must be used wisely.

Last Updated : 31 Oct, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads