Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

What is the difference between TRUNCATE and DROP in SQL?

Mary Okosun

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Difference between TRUNCATE and DROP in SQL

In SQL, the DROP and TRUNCATE statements are commands that are mostly used interchangeably due to the similarities in their functionalities. This answer aims to demonstrate the difference between these two statements and the best-case scenarios to use them.

The DROP statement

DROP is a data definition language (DDL) that can be used to drop database objects in SQL. These objects can be databases, tables, views, and so on. The DROP statement completely deletes the object definition, its content, and its structure.

A DROP statement in SQL removes the object component from the relational database management system (RDBMS). We use the DROP command to free up memory space in the database.

Syntax

The syntax for the DROP command in SQL is:

DROP TABLE our_table_name;
Command to drop a table in SQL

Where our_table_name is the name of the table we intend to drop:

DROP DATABASE our_database_name;
Command to drop a database in SQL

Where our_database_name is the name of the database we intend to drop.

Example

We have a database with a table called EmployeeData.

CREATE TABLE EmployeeData (
ID int,
EmployeeName varchar(255),
EmployeeSalary varchar(255)
);
INSERT INTO EmployeeData (ID, EmployeeName, EmployeeSalary)
VALUES (1, "John Doe", "$1000"),
(2, "Justin Kilner", "$5000"),
(3, "Martha Gray", "$8000");
SELECT * FROM EmployeeData;
DROP TABLE EmployeeData;
SELECT * FROM EmployeeData;

Explanation

  • Lines 1–5: We create an EmployeeData table with the column names ID , EmployeeName, and EmployeeSalary.
  • Lines 7–10: We insert values into the columns. We have three entries for the EmployeeData table.
  • Line 12: We select all the rows and columns from the EmployeeData table.
  • Line 12: We select all the rows and columns from the EmployeeData table.
  • Line 14: We delete the EmployeeData table using the DROP syntax.
  • Line 16: We attempt to select all the rows and columns of the table, but this returns an error saying that the table EmployeeData doesn’t exist because the EmployeeData has been deleted.

The TRUNCATE statement

TRUNCATE is a DDL used to remove existing data from a table in SQL. It is similar to the DELETE FROM statement, except that it doesn’t include the WHERE clause. Unlike the DROP statement which completely removes the table definition and contents, the TRUNCATE statement only removes the contents that are the rows and columns in the table.

The structure and table schema, however, still remain intact. This makes the TRUNCATE operation faster compared to the DROP command. There is usually no memory freed when the TRUNCATE command is used.

Syntax

The syntax for the TRUNCATE command in SQL is:

TRUNCATE TABLE our_table_name;
Command to truncate table

Where our_table_name is the name of the table we intend to truncate.

Example

We have a database with a table called EmployeeData.

CREATE TABLE EmployeeData (
ID int,
EmployeeName varchar(255),
EmployeeSalary varchar(255)
);
INSERT INTO EmployeeData (ID, EmployeeName, EmployeeSalary)
VALUES (1, "John Doe", "$1000"),
(2, "Justin Kilner", "$5000"),
(3, "Martha Gray", "$8000");
SELECT * FROM EmployeeData;
TRUNCATE TABLE EmployeeData;
SELECT * FROM EmployeeData;

Explanation

  • Lines 1–5: We create an EmployeeData table with the column names ID , EmployeeName, and EmployeeSalary.
  • Lines 7–10: We insert values into the columns. We have three entries for the EmployeeData table.
  • Line 12: We select all the rows and columns from the EmployeeData table.
  • Line 14: We truncate the EmployeeData table using the TRUNCATE syntax.
  • Line 16: We select all the rows and columns of the EmployeeData table. The TRUNCATE command only deletes the records of the EmployeeData table from the database. We now have a truncated table like the one shown below:
EmployeeData

ID

EmployeeName

EmployeeSalary

Conclusion

In this answer, we learned about the differences between the DROP and TRUNCATE commands in SQL.

RELATED TAGS

sql

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring