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.
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.
The syntax for the DROP command in SQL is:
DROP TABLE our_table_name;
Where our_table_name is the name of the table we intend to drop:
DROP DATABASE our_database_name;
Where our_database_name is the name of the database we intend to drop.
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;
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 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.
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 or tuples 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.
The syntax for the TRUNCATE command in SQL is:
TRUNCATE TABLE our_table_name;
Where our_table_name is the name of the table we intend to truncate.
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;
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:
ID | EmployeeName | EmployeeSalary |
In this answer, we learned about the differences between the DROP and TRUNCATE commands in SQL.
Free Resources