Home/Tutorials/Database Management Systems/TRUNCATE, DROP and RENAME Query

0 of 3 saved

Database Management Systems2 min read

TRUNCATE, DROP and RENAME Query

Read this chapter, write the program on this page, then pass a short check to open the next one.

Truncate, Drop and Rename DML SQL commands. Truncate deletes all the data in table, drop deletes the table and rename is used to change the name of table.

SQL Truncate, Drop or Rename a Table

In this article, we will learn about DROP TABLE and TRUNCATE TABLE commands in SQL. We will differentiate between them using examples and their properties.

SQL Truncate Table

SQL TRUNCATE TABLE statement removes all the records (complete data) from a table. This command does not destroy the table's structure. When we use TRUNCATE command on a table it (auto-increment) initializes the Primary Key. Once a table is truncated, the changes cannot be rolled back. The TRUNCATE TABLE statement does not affect any of the table's indexes, triggers, or dependencies.

Syntax:

TRUNCATE TABLE table_name;

Example: SQL Truncate Table

The below SQL query will delete all the records from the table Student.

TRUNCATE TABLE Student;

Note: TRUNCATE is similar to DELETE with no WHERE clause. We will look at the DELETE command in another article.

SQL DROP TABLE

SQL DROP TABLE statement completely removes a table from the database. This command will destroy the table structure as well as the data stored in it. Be careful while using this statement on tables because once this statement is executed your data will be lost completely.

Syntax:

DROP TABLE table_name;

Example: Drop a Table

The below SQL query will delete the Student table completely.

DROP TABLE Student;

Example: Drop a Database

The below SQL query will delete the Test database from the system.

DROP DATABASE Test;

Truncate Table vs Drop Table

The main and only difference between DROP TABLE command and TRUNCATE TABLE command is: DROP TABLE can also be used to delete complete table but it deletes table structure too while TRUNCATE TABLE does not delete the structure of the table.

Rename Table

RENAME command is used to set a new name for any existing table. Following is the syntax,

RENAME TABLE old_table_name to new_table_name

Here is an example explaining it.

RENAME TABLE student to students_info;

The above query will rename the table student to students_info.

Conclusion

In this article, we discussed DROP TABLE and TRUNCATE TABLE statements to remove the records of the table completely. We learned their syntaxes with differences using examples.