Skip to content

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.

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

Syntax:

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:

Operation DROP TRUNCATE
Definition Deletes data and its definition, removing the entire structure of the table. Preserves the table structure for further use but deletes all the data.
Usage Eliminates existing complications, aiming to remove all traces of the table from the database. Used to remove all rows from the table, retaining the table structure.
Integrity Constraints Integrity constraints are removed along with the table. Integrity constraints remain intact.
View Impact Since the structure is removed, views related to the table are also removed. The table structure is retained, so views associated with it remain.
Memory Frees up table space from memory. Does not free table space from memory.
Performance Slower due to handling complex dependencies and removing the structure. Faster compared to DROP, as it only removes data rows.

SQL DROP AND TRUNCATE Statement Examples

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

Drop and Truncate

To create this table, write the following queries:

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.