Difference Between DROP and Truncate in SQL
In this article, we will learn about the DROP and Truncate command and the differences between them.
What is DROP Command?
DROP is a command of Data Definition Language which removes or deletes the definition indexes, data, constraints, and triggers from the database tables.
In the Structured Query Language, this statement deletes the elements from the relational database management system (RDBMS).
The DROP command is fast in comparison to the TRUNCATE command in SQL. But, its execution time is slow because it has many complications.
We can also use the DROP keyword with the ALTER TABLE statement for deleting one or more columns from the tables.
The syntax for deleting the database is given below:
In this syntax, we have to specify the name of the database which we want to delete from the system.
Note: Be careful when we delete the database because the DROP command deletes all the tables, indexes, and views included in that database.
The syntax for deleting the table is given below:
In this syntax, we have to specify the name of the table which we want to delete from the database.
The syntax for deleting the multiple tables in one statement is given below:
In this syntax, we can specify multiple tables using a comma.
The syntax for deleting the index is given below:
In this syntax, we have to specify the name of the index just after the INDEX keyword.
Unlike the DELETE command in SQL, database users cannot roll back the data from the table after using the DROP command.
The DROP command frees the tablespace from the storage because it permanently deletes the table and its content from the system.
Example of DROP Command
Let's create a new table for implementing the DROP query in SQL. The following CREATE TABLE query creates the Employee table with five fields:
The following query deletes the created Employee table from the database:
What is TRUNCATE Command?
TRUNCATE is also another command of Data Definition Language. This command removes all the values from the table permanently.
This command cannot delete the particular record because it is not used with the WHERE clause.
Syntax of TRUNCATE command in SQL:
In the above syntax, we have to specify the name of that table whose all records we want to delete from the table.
Example of TRUNCATECommand
Let's create a new table for implementing the TRUNCATE query in SQL. The following CREATE TABLE statement creates the Student_Details table with five columns:
The following SQL queries insert the record of students into the above table using INSERT INTO statement:
Let's see the record of the above table using the following SELECT statement:
The following query removes all the rows from the above Student_Details table:
The following statement verifies that the table is successfully truncated or not:
The following table shows the differences between DROP and TRUNCATE command in Structured Query Language: