Open In App

DROP and TRUNCATE in SQL

Last Updated : 08 May, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

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:

DROPTRUNCATE
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

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.



Previous Article
Next Article

Similar Reads

Difference between DROP and TRUNCATE in SQL
Prerequisite - DROP, and TRUNCATE in SQL 1. DROP : DROP is a DDL(Data Definition Language) command and is used to remove table definition and indexes, data, constraints, triggers etc for that table. Performance-wise the DROP command is quick to perform but slower than TRUNCATE because it gives rise to complications. Unlike DELETE we can’t rollback
2 min read
Difference between DELETE, DROP and TRUNCATE
1. DELETE : Basically, it is a Data Manipulation Language Command (DML). It is used to delete one or more tuples of a table. With the help of the “DELETE” command, we can either delete all the rows in one go or can delete rows one by one. i.e., we can use it as per the requirement or the condition using the Where clause. It is comparatively slower
3 min read
SQL Truncate
SQL Truncate is one of the Data Definition Language (DDL) commands. It deletes all the records in a database table. However, it does not delete the table’s structure; instead, it simply reconstructs the structure. The table’s original structure is retained while all the records are removed. Because it reconstructs the structure of a database, it is
4 min read
Difference between DELETE and TRUNCATE
DELETE is a DML(Data Manipulation Language) command and is used when we specify the row (tuple) that we want to remove or delete from the table or relation. The DELETE command can contain a WHERE clause. If the WHERE clause is used with the DELETE command, then it removes or deletes only those rows (tuples) that satisfy the condition; otherwise, by
4 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Create, Alter and Drop schema in MS SQL Server
In this article, we will be discussing about schema and how to create, alter and drop the schema. 1. Create schema : A schema is usually a collection of objects. The objects can be tables, triggers, views, procedures etc. A database may have one or more schemas. SQL Server provides a feature of pre-defined schemas. The names of pre-defined schemas
3 min read
CREATE and DROP INDEX Statement in SQL
The CREATE INDEX statement will create indexes in tables. Indexes are used for data procurement from the databases faster. The users cannot see the indexes, they are running in the background of queries, used to speed up searches/queries. Create an index on a table : Syntax: CREATE INDEX indexname ON tablename (columnname1, columnname2, ...); Creat
2 min read
TRUNCATE() Function in MySQL
In this article, you will see how the TRUNCATE() function works. The TRUNCATE function in MySQL is used to truncate a number to a specified number of decimal places. Syntax : TRUNCATE( X, D) Parameter : TRUNCATE() function accepts two parameters as mentioned above and described below. X -The number which to be truncated. D -Number of decimal places
5 min read
Truncate All Tables in MySQL
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 a number of integrity-enforcing mechanisms. In SQL, truncate is used to delete all data from the table but doesn't del
3 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
Article Tags :